Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum on Select Criteria
All - Great board with excellent information.
I am looking for help how to sum data for a certain scenario. I have a table of raw data information similar to below. I am trying to put together formulas on another worksheet with columns of months and rows of Accts and sum Col C for the row/col combination. Can anyone help? Thanks! Col A Col B Col C 200601 AcctA $1 200601 AcctB $2 200602 AcctB $3 200603 AcctA $4 200603 AcctB $5 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum on Select Criteria
Hi,
=sumproduct(--(A2:A100="200601")*(B2:B100="AcctA");(c2:c100)) have notice that "200601" i have considered it as a text, if its a general use 200601 or a cell reference (the same for AcctA) hth regards from Brazil Marcelo "toast88" escreveu: All - Great board with excellent information. I am looking for help how to sum data for a certain scenario. I have a table of raw data information similar to below. I am trying to put together formulas on another worksheet with columns of months and rows of Accts and sum Col C for the row/col combination. Can anyone help? Thanks! Col A Col B Col C 200601 AcctA $1 200601 AcctB $2 200602 AcctB $3 200603 AcctA $4 200603 AcctB $5 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum on Select Criteria
200601 200602 200603
AcctA 1 0 4 AcctB 2 3 5 Asumming your next sheet looks like this, your formula in B2 will be =SUMPRODUCT(--(Sheet1!$A$1:$A$5=B$1),--(Sheet1!$B$1:$B$5=$A2),Sheet1!$C$1:$C$5) copy across and down "toast88" wrote: All - Great board with excellent information. I am looking for help how to sum data for a certain scenario. I have a table of raw data information similar to below. I am trying to put together formulas on another worksheet with columns of months and rows of Accts and sum Col C for the row/col combination. Can anyone help? Thanks! Col A Col B Col C 200601 AcctA $1 200601 AcctB $2 200602 AcctB $3 200603 AcctA $4 200603 AcctB $5 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum on Select Criteria
This is the perfect application for a pivot table. Put meaningful names in the row above your first row of data. Month, Account, Money might be correct in this example. In Excel turn on the pivot table toolbar view -toolbar- PivotTable Activate the pivot table wizard, and it will walk you through the process. Step 1. Indicate list. Step 2. When you are asked for a range, specify the labels as well as the data. Step 3. Select new worksheet, and click layout. Drag month and account to the row or column as desired. drag money to the Data ares, it should say "sum of Money. click ok click finish This should be what you want. -- LarryLL ------------------------------------------------------------------------ LarryLL's Profile: http://www.excelforum.com/member.php...o&userid=36136 View this thread: http://www.excelforum.com/showthread...hreadid=559379 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DAVERAGE criteria HELP!!!! | Excel Discussion (Misc queries) | |||
Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
Select rows of data in a worksheet on one criteria in multiple co | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions |