0

I have a table WHERE I need to count if values are equal to "A" OR "B" OR "C" in A1:A7, but only if B1:B7 is "XYZ".

For example: 

A XYZ
G AAA
B ASO
C XYZ
D OOO
F ASO
A ASO

In this example, it should return 2 values, because the first one have A and XYZ and the 4th have C and XYZ. The 3rd and last should not be counted because it doesn't have XYZ even being A or B or C.

Basically I've thinked that a countifs could do it, with multiple countif inside it, but I can't manage it to work. Example:

=COUNTIFS(COUNTIF(A1:A7; "A")+COUNTIF(A1:A7; "B")+COUNTIF(A1:A7; "C"); B1:B7="XYZ)

Can someone help me? Thanks.

Tiago
  • 625
  • 5
  • 16

1 Answers1

1

Either

=COUNTIFS(A:A,"a",B:B,"xyz")+COUNTIFS(A:A,"b",B:B,"xyz")+COUNTIFS(A:A,"c",B:B,"xyz")

or

=SUM(COUNTIFS(A:A,{"a";"b";"c"},B:B,"xyz"))

The curly brackets inside the formula define an array of criterias for the COUNTIFS which will be taken one after the other. The SUM then sums the single results. So this in principle is the same as the above.

Axel Richter
  • 56,077
  • 6
  • 60
  • 87