Excel Count Number of Items if Same Year Across Multiple Worksheet Specific Cell

user_16049786 0
Viewed 47

I have about 50 worksheets, each Worksheet contains specific cell (G11) in date format (dd-mmm-yy); other 5 worksheets used for other purpose.

What I need to do, first, to check cell G11 in the 50 worksheets match to current year and current month, then, count total number of G11 that meets current year and current month across all 50 worksheets. Finally, the counted number will be reflected in separate worksheet.

I had tried SUMPRODUCT or 3-D Reference, but I got return with #VALUE!. A2 is current mmm-yy; Sheet Name in 4 digit numbering:

=SUMPRODUCT(--(TEXT(INDIRECT("'"&Table[Sheet Name]&"'!$G$11"),"mmm-yy")=TEXT(A2,"mmm-yy")))
sumproduct excel-formula
1 Answers

You could do:

=SUM(--(TEXT(N(INDIRECT("'"&Table[Sheet Name]&"'!$G$11")),"mmm-yy")=TEXT(A2,"mmm-yy")))

or

=SUM(--(TEXT(N(INDIRECT(ADDRESS(11,7,,,Table[Sheet Name]))),"mmm-yy")=TEXT(A2,"mmm-yy")))

If you have lower than Excel 2016, you have to do CTRL+SHIFT+ENTER to enter this formula.

user_46171210
Related