SumProduct If?
 

Hi. I need to do a sumproduct on 2 columns (B & C). But, I only want to
include the rows where the contents of column A are "H". So in laymans
terms, scan column A, identify the rows with "H" in column A, and then
perform a sumproduct on those specific rows. Possible? Thanks!

SumProduct If?
 

Hi
If you name the three columns "Text", "DataB" and "DataC" then in a
blank cell type

= sum(if(Text = "H", DataB*DataC,0))

and enter as an array formula using Ctrl+Shift+Enter.

To name a range of cells, highlight it then do Insert, Name,
Define...and give it a name.

You could use the SumProduct function and SubTotal function too, but I
find this "sum if" very flexible if you have several conditions.

regards
Paul

SumProduct If?
 

steph -

add a 3rd item to the sumproduct formula like:

=sumproduct((A10:A100="H")*1,B10:B100,C10:C100)

if A10 .. A100 < "H" then the first item evaluates to zero and will
not impact the sum

SumProduct If?
 

=sumproduct(--(a1:a99="h"),b1:b99,c1:C99)


Steph wrote:

Hi. I need to do a sumproduct on 2 columns (B & C). But, I only want to
include the rows where the contents of column A are "H". So in laymans
terms, scan column A, identify the rows with "H" in column A, and then
perform a sumproduct on those specific rows. Possible? Thanks!

--

Dave Peterson