Sum with multiple criteria using SUMPRODUCT

[Conan Kelly]

Using XL 2003 SP3 on Win XP Pro SP2

--
Hello all,

I have the following formula:

=SUMPRODUCT((Sheet1!$A$2:$A$101=ChartHome)*(Sheet1!$C$2:$C$101=LEFT($B107,2))*(Sheet1!G$2:G$101))

What I want to do is eliminate the first condition
("(Sheet1!$A$2:$A$101=ChartHome)") depending on the value of ChartHome. I
tried the following, but XL didn't like using an IF function inside a
SUMPRODUCT function:

=SUMPRODUCT(IF(ChartHome=16,1,(Sheet1!$A$2:$A$101=ChartHome))*(Sheet1!$C$2:$C$101=LEFT($B107,2))*(Sheet1!G$2:G$101))

I'm guessing that it has something to do with the fact that SUMPRODUCT is an
array function and IF is not. I thought non-array functions would be
converted to array functions when used inside an array function. Guess
not!!!

I know I can accomplish this by having the IF fuction outside and 2
different SUMPRODUCT's inside. I just thought there might be a more
streamlined way of doing it like my example above (IF inside a SUMPRODUCT)

Can anyone think of a more streamlined way other than 2 SUMPRODUCT's inside
an IF?

Thanks for any help anyone can provide,

Conan Kelly

 

[PCLIVE]

Ok, let's try to break this down. With this as a working formula:

=SUMPRODUCT((Sheet1!$A$2:$A$101=ChartHome)*(Sheet1!$C$2:$C$101=LEFT($B107,2))*(Sheet1!G$2:G$101))

How is:
IF(ChartHome=16,1,
supposed to fit in? What is it supposed to do. If ChartHome equals 16,
what are you supposed to do with 1? And if not, what are you supposed to
do?

 

[T. Valko]

*Maybe* this...

Array entered** :

=SUMPRODUCT(IF(ChartHome=16,ROW(A$2:A$101)^0,Sheet1!$A$2:$A$101=ChartHome)*(Sheet1!$C$2:$C$101=LEFT($B107,2))*(Sheet1!G$2:G$101))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)