CODEWITHSUNDEEP
excelexcel-formula
TKE-439
TKE-439

Reputation: 87

Countifs with two options

I am trying to use a countifs to count the places where a store number has either "UE" or "RX" attached to it (in addition to if it matches one other piece of criteria). So for example the criteria would be something like "A16= criteria & "500UE" or "600RX".

Here is the formula I have currently:

=COUNTIFS('AUDIT 1'!$C$3:$C$121,$A16,'AUDIT 1'!$A$3:$A$121,"=*UE")

Now I need to add something to the end of this to the effect of:

,'AUDIT 1'!$A$3:$A$121,"=*RX"

But simply adding that results in no count obviously. I understand that, but I'm not sure what revision (or different formula entirely) I should try.

Thanks in advance!

Upvotes: 0

Views: 135

Answers (2)

Benjamin W
Benjamin W

Reputation: 11

I like Scott Craner's answer, but perhaps you might find it simpler to just add a second countif

=COUNTIFS('AUDIT 1'!$C$3:$C$121,$A16,'AUDIT 1'!$A$3:$A$121,"=*UE")+COUNTIFS('AUDIT 1'!$C$3:$C$121,$A16,'AUDIT 1'!$A$3:$A$121,"=*RX")

(Edited - originally said Scott's proposal used an array formula, which it does not)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152660

Wrap it ins a SUM() and use an array {}.

=SUM(COUNTIFS('AUDIT 1'!$C$3:$C$121,$A16,'AUDIT 1'!$A$3:$A$121,{"*UE","*RX"}))

It basically does two COUNTIFS and sums the two together.

Upvotes: 1

Related Questions