Excel SUMIF formula needed

问题:

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.

Thanks in advance for the help!

回答1:

@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,

回答2:

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)