Forum Discussion

injuredfinch's avatar
injuredfinch
Copper Contributor
Apr 21, 2021

COUNTIF Function

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)

excel

1 Reply

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Apr 21, 2021

    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.

Resources