Countifs with multiple criteria OR

Leticia

New Member
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,223
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Leticia

New Member
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
13,223
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
13,223
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
1625392242929.png


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

Well-known Member
Joined
Dec 30, 2008
Messages
13,223
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=SUM(COUNTIFS($B:$B;H$4&"*";$D:$D;"<>ZZZ";$C:$C;$F$5:$F$6))
 

Leticia

New Member
Joined
Jun 9, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
13,223
Office Version
  1. 365
Platform
  1. Windows
There is already a suggestion there that you haven't responded to.
 

Forum statistics

Threads
1,137,061
Messages
5,679,388
Members
419,825
Latest member
MegastarMagus

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top