COUNTIFS for several criteria with the AND logic.

[Folshot]

Hi all,

Basically I am using the COUNTIF formula to find out if several specific values are present in a range, Below is my formula in cell B1:

Please Login or Register  to view this content.

with cells from range A1 to A10 containing values such as DI1, DI2, DI3...

The issue is that my formula, although it works, can become very long and eventually goes above the maximum number of allowed characters for a cell, hence I am trying to optimise it to make it shorter.

I tried using

Please Login or Register  to view this content.

in my formula but it is for the OR logic, i.e. DI1 or DI2 or DI3.

What I need is the same as above but for the AND logic, i.e. DI1 and DI2 and DI3. Would anyone know the correct syntax?

Thanks.

[6StringJazzer]

Yes, COUNTIFS uses AND logic to join all conditions:

Please Login or Register  to view this content.

[Folshot]

The formula only works if I use one single criteria, i.e.

Please Login or Register  to view this content.

, if I use several criteria it only returns 0 no matter the content of range A1:A10.

[Ace_XL]

You could use this slightly shorter version

=IF(COUNTIF($A$1:$A10,"DI1")*COUNTIF($A$1:$A10,"DI2")*COUNTIF($A$1:$A10,"DI3"),HYPERLINK("#'DI4'!A1","DI4"),
IF(COUNTIF($A$1:$A10,"DI1")*COUNTIF($A$1:$A10,"DI2"),HYPERLINK("#'DI3'!A1","DI3"),
IF(COUNTIF($A$1:$A10,"DI1")>0,HYPERLINK("#'DI2'!A1","DI2"),
HYPERLINK("#'DI1'!A1","DI1"))))

[WideBoyDixon]

Try this (for example), which will tell you if all three values occur in the list:

Please Login or Register  to view this content.

WBD

[Folshot]

Thanks Ace_XL, it works though not reducing it enough.

Thanks WBD, I did a quick test and seems to work great, I am going to implement it. It is very impressive, it reduce the size of the formula so much, very efficient indeed!

[6StringJazzer]

The formula only works if I use one single criteria, i.e.

Please Login or Register  to view this content.

, if I use several criteria it only returns 0 no matter the content of range A1:A10.

Sorry, I got that completely wrong. The formula I provided will count the number of cells that have all three values (in each cell), which of course is zero.