Sum on Select Criteria

T

toast88

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
 
M

Marcelo

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

Sloth

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
 
L

LarryLL

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