Sum of Cells if Row Contains a Specific Variable

[troy.litwiler]

Hi Everyone. I've found tons of help in these forums in the past, and
am hoping I can again. I've put together a spreadsheet for sales
tracking, and need to sum a group of multiple cells in multiple rows,
based on a value in a specific column. An example:

ColumnA ColumnB ColumnC ColumnD
Row1 John 2 3
Row2 Edward 3 5
Row3 Frank 1 2
Row4 John 2 0
Row5 Frank 0 5

I need a formula that will sum the totals from columns B and C, for all
rows that contain "John". Once complete, the above example would look
something like this:

ColumnA ColumnB ColumnC ColumnD
Row1 John 2 3 7
Row2 Edward 3 5 8
Row3 Frank 2 2 9
Row4 John 2 0 7
Row5 Frank 0 5 9

I've tried various iterations of formulas using =COUNTIF, =SUMPRODUCT,
=SUBTOTAL - but so far haven't found a solution. Any suggestions or
hints would be greatly appreciated. Thanks in advance.

 

[Aladin Akyurek]

=SUMIF($A$1:$A$5,A1,$B$1:$B$5)+SUMIF($A$1:$A$5,A1,$C$1:$C$5)

 

[Teeroi]

A thousand thanks. That works perfectly.

 

[jim sturtz]

i believe this would work (from some help i received once)

a more generic variation would be

=SUMIF(A1:INDEX(a:a,bottomrow),A1,b1:INDEX(b:b,bottomrow)) +
SUMIF(A1:INDEX(a:a,bottomrow),A1,c1:INDEX(c:c,bottomrow))

where bottomrow could be a named cell and you would fill it in, or just put
the value of the last row to be used

=SUMIF($A$1:$A$5,A1,$B$1:$B$5)+SUMIF($A$1:$A$5,A1,$C$1:$C$5)

microsoft.public.excel.worksheet.functions