CODEWITHSUNDEEP
excelvbafiltercountif
Kranich_Ibykus
Kranich_Ibykus

Reputation: 95

Excel COUNTIFS: Formula doesn't work

I have a question regarding the formula "=COUNTIFS". I have a table with several columns, one is "Project name" and the others are "category 1" and "category2".

The single "=COUNTIF" works fine for me, see below (I am filtering for the word "Preferential" in two columns on the sheet "Entries":

=COUNTIF(Entries!E2:F1099, "Preferential")

The double one "=COUNTIFS" does not work though. Can you please tell me what I do wrong? This is what I am using:

=COUNTIFS(Entries!E2:F1099, "Preferential", Entries!A2:A1099, "Selling GPI")

I always just get the error "#VALUE!".

Thank you very much!

Upvotes: 0

Views: 1520

Answers (1)

Vincent G
Vincent G

Reputation: 3188

From the documentation found here:

Important: Each additional range must have the same number of rows and columns as the criteria_range1 argument. The ranges do not have to be adjacent to each other.

In your case, the criteria 1 range has 2 columns, while the criteria 2 range has only one.

You can change the formula by :

=COUNTIFS(Entries!E2:E1099, "Preferential", Entries!F2:F1099, "Preferential", Entries!A2:A1099, "Selling GPI")

It should solve your problem. [1]

[1] or maybe not, the second formula implies that "Preferential" should be in both columns E and F

Edit: Corrected formula: If the objective is to count the rows where either E or F are "Preferential" and A is "Selling GPI":

=COUNTIFS(Entries!E2:E1099,"Preferential",Entries!A2:A1099,"Selling GPI")+COUNTIFS(Entries!F2:F1099,"Preferential",Entries!A2:A1099,"Selling GPI")-COUNTIFS(Entries!E2:E1099,"Preferential",Entries!F2:F1099,"Preferential",Entries!A2:A1099,"Selling GPI")

The first COUNTIFS will count rows for columns E and A, the second for F and A, an the third will remove the rows counted twice.

If the objective is to count occurrences of "Preferential" in columns E or F and of "Selling GPI" in column A, you can use:

=COUNTIF(Entries!E2:F1099,"Selling GPI")+COUNTIF(Entries!A2:A1099,"Selling GPI")

Upvotes: 2

Related Questions