Countifs with multiple criteria OR

[Leticia]

Hi! Im calculating a COUNTIFS formula with some OR conditions.
However, although the first condition is met in my result, the second one only calculates the second value ("A").

=IFERROR(IFS($G$4="YES";SUM(COUNTIFS('EV80'!$R:$R;{"R.";"7250"};'EV80'!$F:$F;{"P";"A"};'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<="&$F$4+30;'EV80'!$M:$M;"<"&$D$55;'EV80'!$X:$X;$E$53)));0)

Also, I have another issue. Instead of putting A and P, I would rather link it to a cell, but if I put a cell it gives me an error.

Thank you!

 

[jasonb75]

One slight change needed (unless I've missed something else).
=IFERROR(IFS($G$4="YES";SUM(COUNTIFS('EV80'!$R:$R;{"R.";"7250"};'EV80'!$F:$F;{"P"\"A"};'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<="&$F$4+30;'EV80'!$M:$M;"<"&$D$55;'EV80'!$X:$X;$E$53)));0)

To do the same with cells, you would need A and P in 2 adjacent cells. It can be done with separate cells but it starts getting messy.
Depending on whether the cells used are in the same row or column, you may need to change the first array to {"R."\"7250"} in order to make it work.

 

[Leticia]

One slight change needed (unless I've missed something else).
=IFERROR(IFS($G$4="YES";SUM(COUNTIFS('EV80'!$R:$R;{"R.";"7250"};'EV80'!$F:$F;{"P"\"A"};'EV80'!$G:$G;"<="&$F$4;'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<="&$F$4+30;'EV80'!$M:$M;"<"&$D$55;'EV80'!$X:$X;$E$53)));0)

To do the same with cells, you would need A and P in 2 adjacent cells. It can be done with separate cells but it starts getting messy.
Depending on whether the cells used are in the same row or column, you may need to change the first array to {"R."\"7250"} in order to make it work.

Great thank you so much! I only had to change {"P"\"A"}. Why is that? Apparently, both columns seem the same to me

 

[jasonb75]

When you use ; in both arrays it is pairing the criteria as AND instead of OR. By transposing one of the arrays using \ instead, the formula thinks that part of it is a row instead of a column so it makes uses the criteria as OR instead.

Note that you can only do this with 2 arrays. Any more would need more complex methods.

 

[Leticia]

When you use ; in both arrays it is pairing the criteria as AND instead of OR. By transposing one of the arrays using \ instead, the formula thinks that part of it is a row instead of a column so it makes uses the criteria as OR instead.

Note that you can only do this with 2 arrays. Any more would need more complex methods.

Cool! Out of curiosity, how that would work if for example I wanted to put those conditions - 'EV80'!$Q:$Q;">"&$F$4;'EV80'!$Q:$Q;"<="&$F$4+30 - as a third OR criteria?
Also, could I link P for example to $F$7?

 

[jasonb75]

For 3 or more OR criteria you would need to start looking at sumproduct combined with isnumber(match()), or possibly the filter function if you have it.
The conditions that you currently have in the latter part of the formula would not work with OR criteria anyway, they need to be left as they are. If you set those with OR criteria, the records that to not meet the criteria would be counted and those that do meet the criteria would be duplicated.

could I link P for example to $F$7

If you want P in F7 and A in the formula then you would need to use CHOOSE({1\2},$F$7,"A") in place of {"P"\"A"}

 

[Leticia]

For 3 or more OR criteria you would need to start looking at sumproduct combined with isnumber(match()), or possibly the filter function if you have it.
The conditions that you currently have in the latter part of the formula would not work with OR criteria anyway, they need to be left as they are. If you set those with OR criteria, the records that to not meet the criteria would be counted and those that do meet the criteria would be duplicated.

If you want P in F7 and A in the formula then you would need to use CHOOSE({1\2},$F$7,"A") in place of {"P"\"A"}

Hi! Could I ask you some more doubts? I attach the image of my case simplified as I am not able to use the minisheet

And the code in H5 is =COUNTIFS($B:$B;H$4&"*";$D:$D;"<>ZZZ";$C:$C;AND(LEFT($F5;4);RIGHT($F5;4)))

Issue 1: H$4&"*" does not get it as select if in B it starts with A
Issue 2: When Code has more than 1 values, I cannot select to choose always when some of the 4 digits match C

 

[jasonb75]

You can not use AND as the criteria for functions like countifs, or in any formula where something has to be compare to multiple individual records. In formulas where it does work it would only return a single result if every cell in column C contained both codes, not check each individual row for either of them.

The correct way to do what you are trying would be do have one code in F5 and the other in F6, then use

=SUM(COUNTIFS($B:$B;H$4&"*";$D:$D;"<>ZZZ";$C:$C;$F$5:$F$6))

 

[Leticia]

You can not use AND as the criteria for functions like countifs, or in any formula where something has to be compare to multiple individual records. In formulas where it does work it would only return a single result if every cell in column C contained both codes, not check each individual row for either of them.

The correct way to do what you are trying would be do have one code in F5 and the other in F6, then use

=SUM(COUNTIFS($B:$B;H$4&"*";$D:$D;"<>ZZZ";$C:$C;$F$5:$F$6))

Great! Thank you! could you help me out with this one? EXCLUDE 0 FROM MIN Thank you so much!!

 

[jasonb75]

There is already a suggestion there that you haven't responded to.