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!

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

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

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