countif formula

[Dennisr53]

I have created this countif statement but keep getting an error message when I try to apply it:

=countif(jan!C2+feb!C2+mar!C2+apr!C2+may!C2+jun!C2+jul!C2+aug!C2,"D")

It is supposed to count the total number of D's.... In this case there is a D in each cell on each worksheet so I should have a total answer of 8.

At least that is what I expected to happen instead I get an error message stating "The formula you typed contains an error."

Please help. Thanks, Dennis

 

[Puertorekinsam]

I'm afraid you can't use Countif on non continuous cells.

Excel - Using COUNTIF/COUNTIFS across multiple sheets/same column - Stack Overflow

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(COUNTIF(INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug"}&"!C2"),"D"))

Hope this helps

M.

 

[Dennisr53]

Thank you Marcelo. This worked perfectly. I still need to change the C2 every time I copy and paste the formula but I can work with that if necessary.

I am trying to count the same thing for multiple cell locations. So if I drag and drop the formula it all stays the same and I have to edit the cell location every time.

Thanks again,
Dennis

 

[Dennisr53]

Thanks for the information Puertorekinsam, Marcelo had a solution for me (see below).

 

[Jonmo1]

Try

=SUMPRODUCT(COUNTIF(INDIRECT({"jan";"feb";"mar";"apr";"may";"jun";"jul";"aug"}&"!"&CELL("Address",C2)),"D"))

Now the C2 will incriment as the formula is dragged, and you can apply $absolute rules to the C2 as with any other formula.

 

[Dennisr53]

Jonmo1 thank you this works perfectly. I will have to learn more about array formula's.

 

[Jonmo1]

You're welcome.

Credit to Marcelo though, he did the heavy lifting. I just put in a nail.

 

[Marcelo Branco]

You can also create a named range that contains the sheet names, say SheetList, and using the improvement suggested by Jonmo, try

=SUMPRODUCT(COUNTIF(INDIRECT(SheetList&"!"&CELL("Address",C2)),"D"))

M.

 

[Dennisr53]

I agree a TEAM effort all around is always the best way to go. Thanks to all.