Combining 2 arguments in COUNTIFS function and returning the total of both

Question

I have a large data set that lists out spaces.

I want to count the number of cells containing the values "IT closets" and "Server Rooms" and then retrieve the combined total of both.

The data set has several variations for both i.e. IT Room, IT Closet, Server Room, Server Closet etc.

I was able to count the number of cells that contained the word “IT” by using the following formula:

=COUNTIFS('Spaces Grid'!C:C,"IT*")

So my question is how do I add to this formula to include cells that contain the word “Server*”, and return the combined total of both?

Wrap it in Sumproduct and put your criteria in an array: =SUMPRODUCT(COUNTIF('Spaces Grid'!C:C,{"IT*","Server*"})) — tigeravatar
– tigeravatar, Commented Aug 3, 2022 at 17:16
=COUNTIF('Spaces Grid'!C:C,"IT*")+COUNTIF('Spaces Grid'!C:C,"Server*") should also work — tnavidi
– tnavidi, Commented Aug 3, 2022 at 17:20

JNevill · Accepted Answer · 2022-08-03 17:17:27Z

1

You could do something like:

=SUM(COUNTIFS('Spaces Grid'!C:C,{"it*","server*"}))

This looks for both matches and sums the results.

answered Aug 3, 2022 at 17:17

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Christian Trujillo · Accepted Answer · 2022-08-03 17:20:42Z

0

if you want it to count only the cells that contain "IT closets" and "Server Rooms", you can add 2 countif statements?

=COUNTIF('Spaces Grid'!C:C,"IT closets") + COUNTIF('Spaces Grid'!C:C,"Server Rooms")

let me know if I misunderstood the question

answered Aug 3, 2022 at 17:20

5224 silver badges12 bronze badges

You understood perfectly, and this did the trick. Cheers!