software

How do I sum the following data

ITEMJAN 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.00I 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: www.excelforum.com/member.php...oamp;userid=30684
View this thread: www.excelforum.com/showthread...hreadid=503476This 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))

quot;gerryboy458quot; wrote:

gt;
gt; How do I sum the following data
gt;
gt; ITEMJAN FEB MAR
gt; A 1,000.00 2,000.00 3,000.00
gt; B 2,000.00 3,000.00 3,000.00
gt; C 5,000.00 8,000.00 5,000.00
gt; E 8,000.00 8,000.00 8,000.00
gt; A 10,000.00 10,000.00 10,000.00
gt; C 12,000.00 12,000.00 12,000.00
gt; D 14,000.00 14,000.00 14,000.00
gt; E 16,000.00 16,000.00 16,000.00
gt;
gt;
gt; I would like to create a summary for each item for the quarter in a
gt; table as follows:
gt;
gt; ITEM TOTAL FOR THE QUARTER
gt; A
gt; B
gt; C
gt; D
gt; E
gt;
gt; I have tried all kinds of arrays and SUMIFs without much success. The
gt; best solution so far is something like
gt; 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)
gt; but this is far too long and complicated especially as the real data has
gt; 56 items over 12 months!
gt;
gt; Please help!
gt;
gt;
gt; --
gt; gerryboy458
gt; ------------------------------------------------------------------------
gt; gerryboy458's Profile: www.excelforum.com/member.php...oamp;userid=30684
gt; View this thread: www.excelforum.com/showthread...hreadid=503476
gt;
gt;

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
quot;gerryboy458quot; wrote:

gt;
gt; How do I sum the following data
gt;
gt; ITEMJAN FEB MAR
gt; A 1,000.00 2,000.00 3,000.00
gt; B 2,000.00 3,000.00 3,000.00
gt; C 5,000.00 8,000.00 5,000.00
gt; E 8,000.00 8,000.00 8,000.00
gt; A 10,000.00 10,000.00 10,000.00
gt; C 12,000.00 12,000.00 12,000.00
gt; D 14,000.00 14,000.00 14,000.00
gt; E 16,000.00 16,000.00 16,000.00
gt;
gt;
gt; I would like to create a summary for each item for the quarter in a
gt; table as follows:
gt;
gt; ITEM TOTAL FOR THE QUARTER
gt; A
gt; B
gt; C
gt; D
gt; E
gt;
gt; I have tried all kinds of arrays and SUMIFs without much success. The
gt; best solution so far is something like
gt; 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)
gt; but this is far too long and complicated especially as the real data has
gt; 56 items over 12 months!
gt;
gt; Please help!
gt;
gt;
gt; --
gt; gerryboy458
gt; ------------------------------------------------------------------------
gt; gerryboy458's Profile: www.excelforum.com/member.php...oamp;userid=30684
gt; View this thread: www.excelforum.com/showthread...hreadid=503476
gt;
gt;

Well. Surprise, surprise. You don't have to enter it as an array formula.
It seems to work just fine entered normallyquot;gerryboy458quot; wrote:

gt;
gt; How do I sum the following data
gt;
gt; ITEMJAN FEB MAR
gt; A 1,000.00 2,000.00 3,000.00
gt; B 2,000.00 3,000.00 3,000.00
gt; C 5,000.00 8,000.00 5,000.00
gt; E 8,000.00 8,000.00 8,000.00
gt; A 10,000.00 10,000.00 10,000.00
gt; C 12,000.00 12,000.00 12,000.00
gt; D 14,000.00 14,000.00 14,000.00
gt; E 16,000.00 16,000.00 16,000.00
gt;
gt;
gt; I would like to create a summary for each item for the quarter in a
gt; table as follows:
gt;
gt; ITEM TOTAL FOR THE QUARTER
gt; A
gt; B
gt; C
gt; D
gt; E
gt;
gt; I have tried all kinds of arrays and SUMIFs without much success. The
gt; best solution so far is something like
gt; 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)
gt; but this is far too long and complicated especially as the real data has
gt; 56 items over 12 months!
gt;
gt; Please help!
gt;
gt;
gt; --
gt; gerryboy458
gt; ------------------------------------------------------------------------
gt; gerryboy458's Profile: www.excelforum.com/member.php...oamp;userid=30684
gt; View this thread: www.excelforum.com/showthread...hreadid=503476
gt;
gt;