Sum a range based on a criterion
 

How do I sum the following data

ITEM JAN FEB MAR
A 1,000.00 2,000.00 3,000.00
B 2,000.00 3,000.00 3,000.00
C 5,000.00 8,000.00 5,000.00
E 8,000.00 8,000.00 8,000.00
A 10,000.00 10,000.00 10,000.00
C 12,000.00 12,000.00 12,000.00
D 14,000.00 14,000.00 14,000.00
E 16,000.00 16,000.00 16,000.00


I would like to create a summary for each item for the quarter in a
table as follows:

ITEM TOTAL FOR THE QUARTER
A
B
C
D
E

I have tried all kinds of arrays and SUMIFs without much success. The
best solution so far is something like
SUMIF($C$9:$C$18,F32,$G$9:$G$18)+SUMIF($C$9:$C$18, F32,$H$9:$H$18)+SUMIF($C$9:$C$18,F32,$I$9:$I$18)
but this is far too long and complicated especially as the real data has
56 items over 12 months!

Please help!


--
gerryboy458
------------------------------------------------------------------------
gerryboy458's Profile: http://www.excelforum.com/member.php...o&userid=30684
View this thread: http://www.excelforum.com/showthread...hreadid=503476

Sum a range based on a criterion
 

This worked in a simple test - using your sample data and putting the value
A
into cell A14

=SUMPRODUCT(--($A$2:$A$9=A14),($B$2:$B$9+$C$2:$C$9+$D$2:$D$9))

"gerryboy458" wrote:


How do I sum the following data

ITEM JAN FEB MAR
A 1,000.00 2,000.00 3,000.00
B 2,000.00 3,000.00 3,000.00
C 5,000.00 8,000.00 5,000.00
E 8,000.00 8,000.00 8,000.00
A 10,000.00 10,000.00 10,000.00
C 12,000.00 12,000.00 12,000.00
D 14,000.00 14,000.00 14,000.00
E 16,000.00 16,000.00 16,000.00


I would like to create a summary for each item for the quarter in a
table as follows:

ITEM TOTAL FOR THE QUARTER
A
B
C
D
E

I have tried all kinds of arrays and SUMIFs without much success. The
best solution so far is something like
SUMIF($C$9:$C$18,F32,$G$9:$G$18)+SUMIF($C$9:$C$18, F32,$H$9:$H$18)+SUMIF($C$9:$C$18,F32,$I$9:$I$18)
but this is far too long and complicated especially as the real data has
56 items over 12 months!

Please help!


--
gerryboy458
------------------------------------------------------------------------
gerryboy458's Profile: http://www.excelforum.com/member.php...o&userid=30684
View this thread: http://www.excelforum.com/showthread...hreadid=503476

Sum a range based on a criterion
 

Forgot to mention that

=SUMPRODUCT(--($A$2:$A$9=A14),($B$2:$B$9+$C$2:$C$9+$D$2:$D$9))

is an array formula - commit it by pressing Shift-Ctrl-Enter



"gerryboy458" wrote:


How do I sum the following data

ITEM JAN FEB MAR
A 1,000.00 2,000.00 3,000.00
B 2,000.00 3,000.00 3,000.00
C 5,000.00 8,000.00 5,000.00
E 8,000.00 8,000.00 8,000.00
A 10,000.00 10,000.00 10,000.00
C 12,000.00 12,000.00 12,000.00
D 14,000.00 14,000.00 14,000.00
E 16,000.00 16,000.00 16,000.00


I would like to create a summary for each item for the quarter in a
table as follows:

ITEM TOTAL FOR THE QUARTER
A
B
C
D
E

I have tried all kinds of arrays and SUMIFs without much success. The
best solution so far is something like
SUMIF($C$9:$C$18,F32,$G$9:$G$18)+SUMIF($C$9:$C$18, F32,$H$9:$H$18)+SUMIF($C$9:$C$18,F32,$I$9:$I$18)
but this is far too long and complicated especially as the real data has
56 items over 12 months!

Please help!


--
gerryboy458
------------------------------------------------------------------------
gerryboy458's Profile: http://www.excelforum.com/member.php...o&userid=30684
View this thread: http://www.excelforum.com/showthread...hreadid=503476

Sum a range based on a criterion
 

Well. Surprise, surprise. You don't have to enter it as an array formula.
It seems to work just fine entered normally


"gerryboy458" wrote:


How do I sum the following data

ITEM JAN FEB MAR
A 1,000.00 2,000.00 3,000.00
B 2,000.00 3,000.00 3,000.00
C 5,000.00 8,000.00 5,000.00
E 8,000.00 8,000.00 8,000.00
A 10,000.00 10,000.00 10,000.00
C 12,000.00 12,000.00 12,000.00
D 14,000.00 14,000.00 14,000.00
E 16,000.00 16,000.00 16,000.00


I would like to create a summary for each item for the quarter in a
table as follows:

ITEM TOTAL FOR THE QUARTER
A
B
C
D
E

I have tried all kinds of arrays and SUMIFs without much success. The
best solution so far is something like
SUMIF($C$9:$C$18,F32,$G$9:$G$18)+SUMIF($C$9:$C$18, F32,$H$9:$H$18)+SUMIF($C$9:$C$18,F32,$I$9:$I$18)
but this is far too long and complicated especially as the real data has
56 items over 12 months!

Please help!


--
gerryboy458
------------------------------------------------------------------------
gerryboy458's Profile: http://www.excelforum.com/member.php...o&userid=30684
View this thread: http://www.excelforum.com/showthread...hreadid=503476