# COUNTIFs AND and OR

#### gavs73

##### Board Regular
Hi

Is it possible to have an OR or AND statement in the evaluation part of a COUNTIFs formula ?

Thanks
Gavin

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
AND is the inherent function of countifs.
Example
=COUNTIS(A1:A100,"Car",B1:B100,"House")
That counts how many times Car appears in A1:A100 AND House appears in B1:B100.

But it can't do OR directly, it needs help from some other function.

If you can give a more specific example of what you want, it can probably be done.

Example for doing OR with sum+countif

=SUM(COUNTIF(A1:A100,{"Car","House"}))
That will count how many times Car OR House appears in A1:A100

Hi

Thanks for your help, it’s the final part of the statement that’s causing issues 'Account Doors'!\$L:\$L,OR("<>",">="&B3))

=COUNTIFS('Account Doors'!\$E:\$E,\$A\$4,'Account Doors'!\$K:\$K,"<="&B3,'Account Doors'!\$L:\$L,OR("<>","<="&B3))

-------------------

It should be checking that L:L is empty “<>”

Or if it’s not empty that the date in column L is

<= B3 where B3 is a Date

--------------------

So only counting if the row in Column L is Empty or if it’s not empty that it has a Date <= the date in B3.

Kind Regards
Gavin

Hi.

Are the blanks in 'Account Doors'!\$L:\$L "genuine" blanks? Or perhaps the "" e.g. as a result of formulas in those cells?

Regards

Hi, they are genuine blanks.

Then maybe switch to SUMPRODUCT, though in that case you would be strongly advised to not use entire columns for the references, e.g.:

=SUMPRODUCT(('Account Doors'!\$E2:\$E1000=\$A\$4)*('Account Doors'!\$K2:\$K1000<=B3)*('Account Doors'!\$L2:\$L1000<=B3))

(Change the references accordingly.)

The point being that any blank cells within Account Doors'!\$L2:\$L1000 are first (helpfully, in this case) converted to zeroes by SUMPRODUCT prior to resolving the construction (this would not be the case if some of your blanks were actually ""), and so will evidently be considered less than any date you may have in B4.
<!--[if !supportLineBreakNewLine]-->
Regards

I am currently using this formula to count contact types in a given week: =COUNTIFS(ContactType,\$C5,Weekof,"=" & AJ\$4). It works fine.

Recently the data has changed in two ways 1) changes to spelling of existing contacts types,eg, XTR Agency became TR Agency, 2) replacement of existing contact types, eg, SRC Frontline became PCS Lead. For the meanwhile I need to count both. Inexplicably a modification to my original formula does not work anymore. So I tried these below.

The first is pulling the contact type from list in Sheet1.
=COUNTIFS(ContactType,"=" & Sheet1!F4,Weekof,"=" & AJ\$4,ContactType, "=" & Sheet1!F5,Weekof,"="&AJ\$4)

The second is a modification of your formula:
=SUM(COUNTIF(ContactType,{"XTR Agency","TR Agency"}))
=SUM(COUNTIF(ContactType,{"XTR Agency","TR Agency"},{Weekof,"=" & AJ\$4}))

However, I cannot get the date part to work, ie, weekof... I'd appreciate some help to get this right.
Thanks

Replies
2
Views
150
Replies
7
Views
211
Replies
5
Views
127
Replies
6
Views
170
Replies
2
Views
330

1,220,965
Messages
6,157,122
Members
451,399
Latest member
alchavar

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