Countif - Only with specific cells in a column

[Mustang65]

I am trying to count the times the word "Sunny" appears in a group of specific cells in column "D" and put the total in cell D177. There are 12 cells: D8, D22, D36, D50, D64, D78, D92, D106, D120, D148, D162. I have tried using Count and Countif, but they are designed for use with ranges.

The problem I am facing is that the word "Sunny" can also appear in other cells in that column, which are not to be included in the total.

Thanks again,
Don

[6StringJazzer]

Try this. I would be happy to test it if you provide your file.

SUM(COUNTIF(INDIRECT({"D8,D22,D36,D50,D64,D78,D92,D106,D120,D148,D162"}),"Sunny"))

ETA: Note that this formula is not movable because the range reference is text.

[Mustang65]

Try this. I would be happy to test it if you provide your file.

SUM(COUNTIF(INDIRECT({"D8,D22,D36,D50,D64,D78,D92,D106,D120,D148,D162"}),"Sunny"))

ETA: Note that this formula is not movable because the range reference is text.

Almost got it.


Jeff,
The formula does not work with more than one cell in it. When I remove all the cells, except for D8, it works fine. I tested by changing the contents from Sunny to Shade. If I add the next cell to be looked at D22, I get a #Ref! (Moving of deleting cells caused an invalid cell reference , or function is returning reference error).
The total should be 3.

Thanks
Don

[6StringJazzer]

I forgot that each continuous range must be separated. This is painful but works:

=SUM(COUNTIF(INDIRECT({"D8","D22","D36","D50","D64","D78","D92","D106","D120","D134","D148","D162"}),"Sunny"))

[Mustang65]

PERFECT!!!!!
Thanks
Don