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

#### loopa1

##### Board Regular
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.

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.

Last edited:

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Try this version

#### loopa1

##### Board Regular
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

##### MrExcel MVP
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

##### Board Regular
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.

Replies
3
Views
528
Replies
1
Views
267
Replies
1
Views
664
Replies
6
Views
289
Replies
5
Views
725

1,190,920
Messages
5,983,590
Members
439,852
Latest member
balasat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back