Excel countif not working as expected

1

My forumla is as follows =COUNTIFS(H4:H700,"Jacob",H4:H700,"Tobi",H4:H700,"refresh")

within the range of H4:h700, I have various different text strings. I only want to count when the text strings shown in the formula above appear in this range.

When entering this formula into a cell, say "I2" (which is outside the table), the result is 0. I can confirm that the text strings I want to query are definitely in this range.

Note, the formula in J2 works perfectly =COUNTIF(H4:H986,"USER INSTALLED"),

enter image description here

Any suggestions?

Jacob K

Posted 2017-10-05T12:44:33.063

Reputation: 503

Try this: =COUNTIFS(H4:H700,"=Jacob",H4:H700,"=Tobi",H4:H700,"=refresh") – Kevin Anthony Oppegaard Rose – 2017-10-05T12:51:26.470

No change. Adding the = sign still results in 0. Can I use an OR statement in a situation like this? see my edit to the OP, the formula in J2 which is set up the same way works fine. – Jacob K – 2017-10-05T12:53:40.237

I see, yes. You need to use OR in this case. I would suggest. assuming you only have 3 variables, you simply make 3 IF statements and add them, =IF(H4:H700,"=Jacob")+IF(H4:H700,"=Tobi")+... – Kevin Anthony Oppegaard Rose – 2017-10-05T12:57:34.523

Answers

4

COUNTIFS is AND not OR so it is looking for one cell that has everything you are asking.

You want this:

=SUM(COUNTIF(H4:H700,{"Jacob","Tobi","refresh"}))

Scott Craner

Posted 2017-10-05T12:44:33.063

Reputation: 16 128

This answer is better than my suggestion :) – Kevin Anthony Oppegaard Rose – 2017-10-05T12:57:51.857

1That did it! Thanks! I figured I was on the right track earlier since I was playing with =SUM and =COUNTIF statements. Didn't think to nest them together, and I would've never thought to try brackets. – Jacob K – 2017-10-05T12:58:25.250

1@KevinAnthonyOppegaardRose it is the same in principle but with less typing – Scott Craner – 2017-10-05T12:58:34.253

Asked: 2017-10-05T12:44:33.063

Viewed: 345 times

Active: 2017-10-05T12:57:18.137