COUNTIF Function

Occasional Visitor

Hello I am having trouble understanding how to properly use the COUNTIF function.

I am on PC/Windows 10.

Microsoft 365 Apps for enterprise, Version 2103 (Build 13901.20400 Click-to-Run)


I could upload the file but I would say it would just be easier for me to explain.

I am trying to use the COUNTIF function in such a way that it is checking each cell, say from A1:D4 (an array of numbers). There is an group of numbers like this per sheet, and I have 12 sheets(one per month). 

The new table I am making is checking if out out all the numbers on each sheet if it is between 0<x<=1, then 1<x<=2 and so on up to 29<x<=30. So in total I would have 30 of these COUNTIF functions checking the same 12 groups of numbers, just with different ranges(or criteria for the way the function labels it).


I would like to see if there was a way if the function worked like this:

=COUNTIF((JAN!A1:D4)&(FEB!A1:D4)&...&(DEC!A1:D4), 0<x<=1)

1 Reply



There are several ways you can use to COUNT across sheets.


  • I would like to suggest few example:





  • Where A1:E10 has values to count, F1 has the Criteria.
  • Another is:






  • Where A2:A4 is range has the Sheet Name, and B2:B10 has values to count, E2 has the Criteria.

One more is:





  • Where the SheetName is Named Range has Sheet Name, and A2:D100 has values to count, E2 has the Criteria.


  •  Adjust cell references in the formula as needed.