COUNTIFs AND and OR

[gavs73]

Hi

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

Thanks
Gavin

 

[Jonmo1]

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

 

[gavs73]

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

 

[XOR LX]

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

 

[gavs73]

Hi, they are genuine blanks.

 

[XOR LX]

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

 

[isadoko]

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