How to include an OR statement in a =SUM(IF(...

[loopa1]

Hi all, I have the following formula which I'd like to improve/shorten by using an OR statement rather than having to sum two 'parts'. Although the formula works in it's current format, as I add more criteria too it, it because too long for excel to handle.

{=SUM(IF(ISNUMBER('Caseload'!$A$2:$A$10000),(DATE(YEAR($H$8),MONTH($H$8)+1,0)>='Caseload'!$AC$2:$AC$10000)*("Previously attended"='Caseload'!$AF$2:$AF$10000)))+SUM(IF(ISNUMBER('Caseload'!$A$2:$A$10000),(DATE(YEAR($H$8),MONTH($H$8)+1,0)>='Caseload'!$AC$2:$AC$10000)*("Currently attending"='Caseload'!$AF$2:$AF$10000)))}

What I'm trying to achieve is the following:

if 'Caseload'!$A$2:$A$10000 is a number (which will ALWAYS be the case)
then
count the number of records where the date they were taken on to the caseload is less than the final day in the reporting month (see note 1)
and the individual was 'previously attending' the clinic
or the individual is 'currently attending' the clinic.

note 1 - given the 1st of the month is always used whenever a user inserts a month and year, for example, 01/03/2011 would be used if the user typed 'Mar-11' in to excel, the following formula returns the last day in march and compares it to the date the individual was taken on to caseload: (DATE(YEAR($H$8),MONTH($H$8)+1,0)>='Caseload'!$AC$2:$AC$10000)

So you can see that the two 'parts' of my formula only differ by the use of 'currently attending' and 'previously attended'.

Is there a way of combining this by using an OR statement?

I've tried using a sumproduct, but couldn't get that to work seen as though I'm not returning or summing any values - I just want to count the number of records matching this criteria.

Thanks.

 

[barry houdini]

Try this version

=SUMPRODUCT(ISNUMBER('Caseload'!$A$2:$A$10000)*(DATE(YEAR($H$8),MONTH($H$8)+1,0)>='Caseload'!$AC$2:$AC$10000)*ISNUMBER(MATCH('Caseload'!$AF$2:$AF$10000,{"Currently attending","Previously attended"},0)))

 

[loopa1]

Works a treat Barry - thanks.

Would you mind explaining the formula to me?

I don't understand the final isnumber(match.... bit. I understand that this part of the formula counts those cells which match the two attendance types, but I don't know how.

Thanks.

 

[barry houdini]

MATCH finds a value in a single column (or row) range so in its simplest form

=MATCH("x",A2:A10,0)

then that formula returns the position of the first "x" in A2:A10, so if there is only one "x" in the range, in A7, then the formula returns 6 (because A7 is the 6th cell in A2:A10)....if "x" isn't found the formula returns an error.....so if we extend that to

=ISNUMBER(MATCH("x",A2:A10,0))

then the formula now returns either TRUE (when MATCH returns a number because "x" is in the range) or FALSE (when "x" isn't in the range).

In your case the lookup range in my example is replaced by just the two values {"Currently attending","Previously attended"}, so the ISNUMBER(MATCH part is effectively giving you your "OR" function. You can extend it to check against any number of values....

 

[loopa1]

Ah I see.

So the final bit returns a true or false flag, which is used in part of the rest of the sum product formula.

So...
yes it's a number...yes the date is fine...yes it's a 'true flag'...so count this
or (for example)
yes it's a number...yes the date is fine...no it's a 'false flag', so ignore it.

and so on.