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

@injuredfinch 

 

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

 

  • I would like to suggest few example:

 

=SUMPRODUCT(COUNTIF(INDIRECT({"'First'!A1:E10";"'Second'!A1:E10";"'Third'!A1:E10";
"'Fourth'!A1:E10"}),F1))

 

N.B. 

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

 

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!B2:B10"),E2))

 

 

N.B. 

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

One more is:

 

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetName&"'!A2:D100"),E2))

 

N.B.

  • 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.