HELP with countif and filters

[micky147]

Hi, I have a spreadsheet where column B lists the shifts (Days, Earlies or Nights) for 102 employees. Columns G to AK represent each day of the month for August. If someone is on holiday, I enter V in the cell of each day they are off.

I have entered =COUNTIF(G4:G105,"V") to tell me how many are on holiday each day, but when I filter column B to get the totals for each shift, it still counts the hidden cells and returns the same total.

Help please!

 

[Marcelo Branco]

Hi,

Are there only Vs and blank-cells in the columns?

If so you can use
=SUBTOTAL(3,G$4:G$105)
copy across

If there are others values like, say P for present or something like that, use
=SUMPRODUCT(SUBTOTAL(3,OFFSET($B$4:$B$105,ROW($B$4:$B$105)-ROW($B$4),0,1)),--(G$4:G$105="V"))
copy across

Assuming your data in rows 4 till 105

HTH

M.

 

[micky147]

Thankyou very much for your quick response, it works fine now, much appreciated

 

[Marcelo Branco]

Thankyou very much for your quick response, it works fine now, much appreciated

You are welcome and tks for the feedback

M.