Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

COUNTIF ACROSS MULTIPLE SHEETS

68 views
Skip to first unread message

Ms. Mills

unread,
Jun 27, 2022, 7:42:42 AM6/27/22
to
Good day,
I used excel to create report cards for my school. Each student's report card is on a different worksheet in the workbook.
I am now at the point where I need to count the number of As, Bs, Cs etc for each subject in the workbook. Is there a formula I could use to do this?
I have tried the following:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$P$1:$P$42&"'!"&CELL("address",H11)),"A"))

P1:P42 - list of the sheet names
H11 - the cell where the letter grade appears (For example, all the English Language grades appear in H11 for each report card).
"A" - the letter grade being counted.

Claus Busch

unread,
Jun 27, 2022, 8:46:07 AM6/27/22
to
Hi,
try:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&P1:P4&"'!H11"),"A"))


Regards
Claus B.
--
Windows10
Microsoft 365 for business

Claus Busch

unread,
Jun 27, 2022, 8:47:36 AM6/27/22
to
Hi,

Am Mon, 27 Jun 2022 14:46:05 +0200 schrieb Claus Busch:

> try:
> =SUMPRODUCT(COUNTIF(INDIRECT("'"&P1:P4&"'!H11"),"A"))

sorry, wrong range.
Try:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&P1:P42&"'!H11"),"A"))

Ms. Mills

unread,
Jun 27, 2022, 9:02:47 AM6/27/22
to
Thank you! The formula worked for the range P1:P5 but would return #REF once I put in the full range. Is there something else I need to add or does this only work for a certain number of worksheets? I have 40 worksheets in total.

Claus Busch

unread,
Jun 27, 2022, 11:16:03 AM6/27/22
to
Hi,

Am Mon, 27 Jun 2022 06:02:43 -0700 (PDT) schrieb Ms. Mills:

> Thank you! The formula worked for the range P1:P5 but would return #REF once I put in the full range. Is there something else I need to add or does this only work for a certain number of worksheets? I have 40 worksheets in total.

then one or more table names are missing or there is a typo.

Philip Herlihy

unread,
Jun 28, 2022, 6:54:31 AM6/28/22
to
In article <72c1786c-6f05-47e9...@googlegroups.com>, Ms. Mills
wrote...
In any goal that looks like this my first thought is to harness the Pivot Table
approach, for power, elegance and avoidance of errors. Leila Garahi (I'm a big
fan) has excellent videos on Pivot Tables. See first:
https://www.xelplus.com/pivot-tables-in-10-minutes/

Can you build Pivot Tables across worksheets? Apparently so!
https://support.microsoft.com/en-us/office/consolidate-multiple-worksheets-
into-one-pivottable-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5

--

Phil, London
0 new messages