Excel 2008 : Can I nest an IF function in a SUMPRODUCT function

I have Excel 2010. In the formula below I need to sort rows of data by conditions in the columns. In the bold section below I have tried to nest an IF function in a SUMPRODUCT function. I get the correct answers in the fx box but it displays #VALUE! in the cell.

What I need to do is filter rows by the value in a reference cell or if no value in the reference cell don't filter the column (Include all rows with data in that column). I need to do this with several more columns.


=SUMPRODUCT(--IF(AH$31>0,(F$6:F$204=AH$31),(F$6:F$204>0)),--(E$6:E$204+((E$6:E$204<AH$23)*(AH$23>AH$24))>=AH$23),--($E$6:$E$204+(($E$6:$E$204<AH$23)*(AH$23>AH$24))<=(AH$24+(AH$23>AH$24))),--(G$6:G$204=AI31))

When you embed an IF into a SUMPRODUCT invariably it necessitates Array entry ... this is why for ex. you get the correct answer in fx but not in cell - the former evaluates as an Array whereas the latter is not (presently).

Goes without saying that if you use SUMPRODUCT with Array entry then generally speaking you may as well just use an Array as it's more flexible than SUMPRODUCT
(the advantage of SUMPRODUCT is that it doesn't require Array entry - it's not really any more efficient)

Without seeing a sample it's hard to know if a SEARCH approach would suffice but in the meantime the below should work I think:

SIGN(((F$6:F$204=AH$31)*(AH$31>0))+((F$6:F$204>0)*(AH$31=0)))

the above [in essence an OR(AND(),AND()) and the SIGN is used to avoid double counting] and is to replace the below in full:

--IF(AH$31>0,(F$6:F$204=AH$31),(F$6:F$204>0))

So you end up with:

=SUMPRODUCT(SIGN(((F$6:F$204=AH$31)*(AH$31>0))+((F$6:F$204>0)*(AH$31=0))),(E$6:E$204+((E$6:E$204<AH$23)*(AH$23>AH$24))>=AH$23),($E$6:$E$204+(($E$6:$E$204<AH$23)*(AH$23>AH$24))<=(AH$24+(AH$23>AH$24))),--(G$6:G$204=AI31))

note I removed some superfluous double unary coercion

Originally Posted by ccpsc

The full code didn't work

Post a sample to illustrate.

Originally Posted by ccpsc

Can this be corrected so I don't have to enter anything in unused columns? I hope this makes sense.

Post a sample.

Originally Posted by ccpsc

I like the double uraries as they seperate the code and make it easier to read.

So be it but you are performing needless calculations.

edit: point re: providing sample is: yes - I am pretty sure we can improve the formula (and add flexibility) but though we can interpret what you're doing it's much easier to see in action.

If we assume you need to be able to cater for time period of interest to fluctuate between same day and overnight (thereby precluding single COUNTIFS) then one possibility to account for blanks etc...
[ie K & L can be blank - no need for "a"]

=SUMPRODUCT(--ISNUMBER(SEARCH($T$25,$F$6:$F$204&"^")+SEARCH($T$26,$G$6:$G$204&"^")+SEARCH($T$27,$H$6:$H$204&"^")+SEARCH($T$28,$I$6:$I$204&"^")+SEARCH($T$29,$J$6:$J$204&"^")+SEARCH($T$30,$K$6:$K$204&"^")+SEARCH($T$31,$L$6:$L$204&"^")),--($E$6:$E$204+(($E$6:$E$204<$T$23)*($T$23>$T$24))>=$T$23),--($E$6:$E$204+(($E$6:$E$204<$T$23)*($T$23>$T$24))<=($T$24+($T$23>$T$24))))

the above would return 2 per your sample with T30:T31 left blank
(were you to clear say T25 the result would be 4)