Apr 20 2021 09:58 PM
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)
Apr 20 2021 11:09 PM - edited Apr 20 2021 11:23 PM
There are several ways you can use to COUNT across sheets.
=SUMPRODUCT(COUNTIF(INDIRECT({"'First'!A1:E10";"'Second'!A1:E10";"'Third'!A1:E10";
"'Fourth'!A1:E10"}),F1))
N.B.
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!B2:B10"),E2))
N.B.
One more is:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetName&"'!A2:D100"),E2))
N.B.