Count the number of times a value appears across multiple sheets displaying a total

Are you willing to change the sheet names to just the day of the month numbers without the ordinal suffix?

OK, try this...

Create this named formula.
Goto the Formulas tab>Define name
Name: SheetNames
Refers to: =TRANSPOSE(ROW(INDIRECT("1:31")))
OK out

This will create a horizontal array of the numbers 1 to 31 representing the sheet names.

In your sample file which I will attach to the post, this named formula is:

=TRANSPOSE(ROW(INDIRECT("2:5")))

Because in the sample file the sheet names of interest are 2, 3, 4 and 5.

On the Total sheet...

Enter this formula in A2:

=MIN('1:31'!C2:C8)

Enter this array formula** in A3. This will list the distinct ID numbers in ascending order.

=IFERROR(1/(1/MIN(IF(N(OFFSET(INDIRECT("'"&SheetNames&"'!C2:C8"),ROW(C$2:C$8)-ROW(C$2),))>A2,N(OFFSET(INDIRECT("'"&SheetNames&"'!C2:C8"),ROW(C$2:C$8)-ROW(C$2),))))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in B2 and copy down to B3. This will return the counts of the ID numbers.

=IF(A2="","",SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!C2:C8"),A2)))

Select A3:B3 and copy down until you get blanks.

Here's your file with this implemented:

Philbe.xlsx

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.