sum with multiple criteria

[Todd]

I have a table of data I need to group, then sum, according to multiple criteria and then to put into another table.


Acct Num. Description Period Balance Period ID
000 Amort Finance Costs Interim Fac $0.00 1
000 Bank Charges $0.00 1
000 Computer HW Costs $0.00 1
000 Dep - Computer Hardware $0.00 1
000 Dep - Computer Software $0.00 1
000 Depn - Office Eqpt $0.00 1

A9 contains the Account # to search for
A16 contains the period date to search for

There are 18 account numbers and 12 periods

Here is the formula I have so far. Of course, it doesn't work!!

=SUMPRODUCT(IF('Actuals Data'!B:B=A9,IF('Actuals Data'!E2:EE23000=1,IF(A16,'Actuals Data'!D))))


Thanks so much,


Todd

 

[Frank Kabel]

Hi Todd
try
=SUMPRODUCT(--('Actuals Data'!B2:B1000=A9),--('Actuals
Data'!E2:E1000=1),--('Actuals Data'!D21000=A16),C2:C1000)

note: SUMPRODUCT does not accept ranges like A:A. Also all ranges must
have the same dimension

 

[Todd]

Hi Frank,

Thanks so much. Its not quite right yet. Here is what I used.

=SUMPRODUCT((('Actuals Data'!$B$2:$B$1000='0110-000'!$A$9)+('Actuals Data'!$E$2:$E$1000=1)*($C$2:$C$1000=A16)))

I don't know if this might be a problem. Cell a9 would = "000" or"900" and the cells in B2:B1000 are the result of a formula.
=MID(A2,7,3)


Can't wait to get this right. Thanks a lot.


Todd

 

[Todd]

I could probably tell you what it is returning? I am getting numbers 1 through 6. Its not summing the column D yet

Tod

 

[Frank Kabel]

Hi
why not just adapting my initial formula
=SUMPRODUCT(--('Actuals Data'!B2:B1000='0110-000'!A9),--('Actuals
Data'!E2:E1000=1),--('Actuals Data'!C2:C1000=A16),D21000)

this sums column D

 

[Todd]

Now don't I look silly. I was to frustrated and did not
see the obvious. Hey, I am still trying to figure out
when to choose "+" or "*" and as a connector between
formula's. Now you are using "--" ? You have skills!

Its will be Tuesday before I get to work on this again.
Have a good weekend and my guess is I will be asking new
questions by then.

Todd.

 

[Frank Kabel]

Hi
just as explanantion:
you could either use
=SUMPRODUCT((rng1=cond1)*(rng2=cond2))
or
=SUMPRODUCT(--(rng1=cond1),--(rng2=cond2))
Both are creating an AND condition. That is cond1 AND cond2 have to be
met. Just a different syntax. The double minus coerces the boolean
values to a number (TRUE=1/FALSE=0). In the first formula this
conversion is done by multiplying your two conditions.


You could use the '+' operator to mimic an OR condition:
=SUMPRODUCT((rng1=cond1)+(rng1=cond2))
In this case rng1 has to met either cond1 or cond2 (assumption: these
conditions are mutually exclusive). If you have and OR condition for
two separate columns better to use something like
=SUMPRODUCT(--((rng1=cond1)+(rng2=cond2)>0))
this makes sure that if BOTH conditions are met they are not counted as
'2' but also as '1'