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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

So you end up with:

Please Login or Register  to view this content.

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"]

Please Login or Register  to view this content.

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

That works fine, thanks DonkeyOte. How does ISNUMBER(SEARCH($AH$25,$F$6:$F$204&"^") work inparticular the "^"?

First there appears to be a typo in my prior suggestion - the last SEARCH range should refer to Col L rather than K (which is repeated twice over)

Originally Posted by ccpsc

How does ISNUMBER(SEARCH($AH$25,$F$6:$F$204&"^") work

SEARCH will return either a number (the char position of the criteria in the searched string) or #VALUE! error (not found)

It follows that an ISNUMBER will convert the above to a Boolean - TRUE (found) or FALSE (not found).

Given the above we need not apply ISNUMBER to each SEARCH result - we can simply try adding all SEARCH results together and then test the final result.
(TRUE only if no one SEARCH returned #VALUE!)

Originally Posted by ccpsc

...inparticular the "^"?

Given you have blanks at source and wish for blank criteria value (or *) to equate to "all ok" you can tweak the source values by adding ^ to each.
The addition of the ^ does not affect the search where criteria is specified [note 1] but ensures no source value is truly blank when evaluated.
Per your "a" the SEARCH will always return a number when criteria is blank (or *)


note 1: we select a char that would never:

a) appear in criteria string
and
b) appear in the strings to be Searched

Given the blank filters are handled in that manner for a good reason all you need do to exclude blank rows is validate the Time.

You can do this very simply by adding an ISNUMBER test:

Please Login or Register  to view this content.

If you add the above to your SUMPRODUCT you will get your 4.