Excel SUMIF formula needed

1
votes

I'm looking for a SUMIF formula which adds up all values of a column if the row meets a certain criteria. The criteria is that it needs to match either of the 3 categories (A, B or C). My data looks like this:

A     5     4
B    32     4
B    12     6
B     4     7
A    21   342
C     2     2
C     1     1
A     3     5

A   <total A> <total A> etc
B   <total B> <total B> etc
C   <total C> <total C> etc

The formula should work when dragged horizontally.

Screenshot of an example table

Thanks in advance for the help!

2
excelexcel-formula

2 Answers

1
votes

@Mike Meinz gave you the right answer. Formula could be simplified like the following:

In Column B totals for criteria A, B and C can be calculated as following:

=SUMIF($A1:$A8,"A",$B1:$B8) 
=SUMIF($A1:$A8,"B",$B1:$B8)
=SUMIF($A1:$A8,"C",$B1:$B8)

Note: absolute reference ($) is used to make the value stay the same if cell is dragged horizontally, as per your requirement.

In case you would like to calculate the totals of current column after the cell was dragged (rather than fixed one, i.e. column B in this example), modify the formula like:

=SUMIF($A1:$A8,"A",B1:B8)

Correspondingly, similar syntax can be applied to Column C. Regards,

1
votes

In Column B

=SUMIF($A$1:$A$8,"=A",B$1:B$8)
=SUMIF($A$1:$A$8,"=B",B$1:B$8)
=SUMIF($A$1:$A$8,"=C",B$1:B$8)

In Column C

=SUMIF($A$1:$A$8,"=A",C$1:C$8)
=SUMIF($A$1:$A$8,"=B",C$1:C$8)
=SUMIF($A$1:$A$8,"=C",C$1:C$8)