PDA

View Full Version : Solved: UDF, VBA or Formula for multi-conditional SUMIF?



Marcster
06-22-2009, 10:31 AM
Hi People,

I have two sheets in an Excel 2003 file.
On Sheet1 I want to sumif with conditions, the data is on sheet "6 CP".
Do not know how to go about this. What I'm after is:
In cell D9 on sheet Sheet1:
total of
Col L +
Col Q +
Col T +
Col U
where
Col J = 0
and Col E = "DEC TM ACC"
on sheet(6 CP)

In cell E9 on sheet Sheet1:
total of
Col L +
Col Q +
Col T +
Col U
where Col J <> 0
and Col E = "DEC TM ACC"
on sheet(6 CP)

In cell F9 on sheet Sheet1:
total of
Col L +
Col U
if
Cell D9, above (Sheet1 D9) is non-zero
and Col E = "DEC TM ACC"
on sheet(6 CP)

Don't know weather this can be done with formulas, UDF's or VBA?. :dunno
Any will do, just as long as the results are what I'm after.
Attached file for sample data and info.

Bob Phillips
06-22-2009, 10:52 AM
=SUMPRODUCT(--('6 CP'!$J$2:$J$20=0),--('6 CP'!$E$2:$E$20="DEC TM ACC"),'6 CP'!$L$2:$L$20+'6 CP'!$Q$2:$Q$20+'6 CP'!$T$2:$T$20)

=SUMPRODUCT(--('6 CP'!$J$2:$J$20<>0),--('6 CP'!$E$2:$E$20="DEC TM ACC"),'6 CP'!$L$2:$L$20+'6 CP'!$Q$2:$Q$20+'6 CP'!$T$2:$T$20)

=SUMPRODUCT(--('6 CP'!$J$2:$J$20=0),--('6 CP'!$E$2:$E$20="DEC TM ACC"),'6 CP'!$L$2:$L$20+'6 CP'!$U$2:$U$20)

Marcster
06-23-2009, 10:51 AM
Nice one XLD :beerchug: .
Managed to do some more =SUMPRODUCTS based on the above too.