Formula Excluding Hidden Cells

**Watson** · 04-08-2010, 06:48 AM

Hi,

I am trying to figure out if a hidden group of cells can be excluded from a formula.

Basically, I have a column which counts incidents each month.

So A1 - A50 has a total count of 50.

A1 - A25 is March, and A26 - A50 is April. So if I hide A1-A25, would it be posible to have my count formula to update automatically to remove the hidden cells?

This would help me so that I could just add data each month, instead of having to copy paste and delete. + I could run reports for the whole year when needed.

Kind Regards

Colin

**rwgrietveld** · 04-08-2010, 06:53 AM

Look into subtotal

Function number from 101 till 111 will exclude hidden cells
Function number from 1 till 11 will include hidden cells

**dominicb** · 04-08-2010, 06:55 AM

Good morning Watson

Originally Posted by Watson

I am trying to figure out if a hidden group of cells can be excluded from a formula.

Something like :
=SUBTOTAL(109,A1:A50)
should work fine. You don't state what version of Excel you are using - I seem to remember that SubTotal was beefed up after XL2000, if you're still using that version.

HTH

DominicB

**Watson** · 04-08-2010, 08:49 AM

Sorry, I am using Excel 2007. Subtotal 102 works for me, as this is excluding the cells that I require. I use a few different functions, what with Count/sum etc. But Subtotal defo works.

I can't seem to use the CountIF function however
thanks guys

**dominicb** · 04-08-2010, 12:33 PM

Hi Watson

Originally Posted by Watson

I can't seem to use the CountIF function however
thanks guys

Can you explain exactly what you're trying to do with CountIf?

DominicB

**Watson** · 04-09-2010, 11:37 AM

I have 2 cells that read from the same column. Basically in the column you can select from a drop down menu, and the drop down either says Yes or No. So I do a Count if to see How Many say Yes, and How many say No using

=COUNTIF(Iceland!H7:H21,"Yes")
=COUNTIF(Iceland!H7:H21,"No")

Regards

Colin

**DonkeyOte** · 04-09-2010, 12:04 PM

The simplest solution in such instances is to use a 1/0 index to indicate visible status... eg - in a blank column on row 7 (we'll assume Z for sake of demo.)

Z7: =SUBTOTAL(103,H7)
copied down for all rows (to Z21)

Your COUNTIF thus becomes a SUMIF

=SUMIF($H$7:$H$21,"Yes",$Z$7:$Z$21)

the above will thus identify all Yes' visible or not but the value in Z will only 1 where the row is visible (ie Yes/invisible -> 0)

Alternatives involve SUMPRODUCT/SUBTOTAL & OFFSET... the above index is obviously simpler and may prove useful for other calcs also.

**Watson** · 04-13-2010, 06:26 AM

Confusing at 1st. But I understand it, and it works a treat! This should also help me with some of my other calculations also. Magic Donkey, thanks very much for this. It should cut down my monthly reports from around a few hours to a few minutes

Thanks again