# Countifs with multiple criteria OR

#### Leticia

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### jasonb75

##### Well-known Member
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

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

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

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

##### Well-known Member
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
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
There is already a suggestion there that you haven't responded to.

Replies
6
Views
136
Replies
3
Views
100
Replies
49
Views
627
Replies
12
Views
268
Replies
12
Views
197

### Forum statistics

1,141,062
Messages
5,704,060
Members
421,325
Latest member
tapete86 ### 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