1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • COUNTIF ACROSS MULTIPLE SHEETS

    From Ms. Mills@21:1/5 to All on Mon Jun 27 04:42:38 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Mon Jun 27 14:46:05 2022
    Hi,

    Am Mon, 27 Jun 2022 04:42:38 -0700 (PDT) schrieb Ms. Mills:

    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.

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


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ms. Mills@21:1/5 to claus...@t-online.de on Mon Jun 27 06:02:43 2022
    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.
    On Monday, June 27, 2022 at 8:47:36 AM UTC-4, claus...@t-online.de wrote:
    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"))
    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Mon Jun 27 14:47:34 2022
    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"))


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Mon Jun 27 17:16:00 2022
    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.


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Philip Herlihy@21:1/5 to All on Tue Jun 28 11:54:27 2022
    In article <72c1786c-6f05-47e9-9fe7-24503a6fde43n@googlegroups.com>, Ms. Mills wrote...

    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.

    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)