CODEWITHSUNDEEP
excelcountif
Rusty Lemur
Rusty Lemur

Reputation: 1875

Excel COUNTIFS with wildcard on numeric data

Can Excel COUNTIFS function use a wildcard to match numeric data? E.g. with the following contrived table:

enter image description here

I want to have a dropdown menu using Data Validation that allows the user to select 1, 2, 3, or * (wildcard). Then Excel should count the number of Ringtailed lemurs that equal the selected Age, where * will result in counting all the Ringtailed lemurs.

If the user selects 1, the output will be 2. If the user selects 2, the output will be 1. If the user selects 3, the output will be 0. And if the user selects *, the output will be 3.

This formula counts the number of Ringtailed lemurs when Age is 1

=COUNTIFS(A2:A7,1,B2:B7,"Ringtailed")

But this formula does not count the total number of Ringtailed lemurs:

=COUNTIFS(A2:A7,"*",B2:B7,"Ringtailed")

My problem is that COUNTIFS doesn't seem to allow wildcard to match any numeric value, but only any text value. If I enter the Ages as '1, '2, '3, '1, '2, '3, then the above formula works. But I can't reformat the Age reference.

Upvotes: 0

Views: 1068

Answers (1)

Solar Mike
Solar Mike

Reputation: 8375

So went with my comment as so:

enter image description here

=SUMIFS(A2:A7,A2:A7,D2,B2:B7,D4)

Cells D2 and D4 have data validation drop downs that allow selection of items in the lists.

Using countifs gives a different result:

COUNTIFS(A2:A7,D2,B2:B7,D4)

And adding * to the list of types will count all of them. Note the rows should be down to 7 not like the image that shows 6.

Upvotes: 1

Related Questions