What to Use Instead of Count If on Multiple Worksheets

[Manchester Gunners]

I am trying to obtain a total from Cell A1 on twelve annual worksheets named January - December providing the cell is less than 4.

I know that I can do this: COUNTIF('January'!A1,"<4")+COUNTIF('February'!A1,"<4")+ etc....

Is there a better way? I have read that there isn't a VBA equivalent to COUNTIF

I can add an example sheet if necessary.

[windknife]

An easy sample file to add A1 of 1-4th months. You can modify according to your needs.

C2:C5
January
February
March
April

D1
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C5&"'!A1"),"<4"))

[John Vergara]

Hi Manchester Gunners!

If your values are integers, you could use:

=INDEX(FREQUENCY('January:December'!A1,3),1)  

If have decimals, just add a number of decimals after the 3:

=INDEX(FREQUENCY('January:December'!A1,3.999),1)  

Blessings!

[Manchester Gunners]

Hi Manchester Gunners!

If your values are integers, you could use:

=INDEX(FREQUENCY('January:December'!A1,3),1)  

If have decimals, just add a number of decimals after the 3:

=INDEX(FREQUENCY('January:December'!A1,3.999),1)  

Blessings!

I'm sorry but I don't understand PHP. I use it on my web pages but have never seen it with excel. Do I just copy and paste it onto the VBA page?

[Manchester Gunners]

An easy sample file to add A1 of 1-4th months. You can modify according to your needs.

C2:C5
January
February
March
April

D1
=SUMPRODUCT(COUNTIF(INDIRECT("'"&C2:C5&"'!A1"),"<4"))

That seems fine. I guess I just add teh remaining months and change C5 to C13

[John Vergara]

Hi again Manchester...

the PHP is just a code tag. Is a normal Excel formula that you can copy to your worksheet. I like the PHP style for the colors instead of another tags. Blessings!