| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
| Match function...random search? | Excel Worksheet Functions | |||
| Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
| Adding colour to a range of cells based on one of the cells v... | Excel Discussion (Misc queries) | |||
| Need to find Min value based on date range entered | Excel Worksheet Functions | |||
Linear Mode
