+ Reply to Thread
Results 1 to 9 of 9

Aggregate statistics across multiple sheets if multiple critereon met

  1. #1
    Registered User
    Join Date
    03-07-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    6

    Exclamation Aggregate statistics across multiple sheets if multiple critereon met

    Hi,

    I was here yesterday and you guys were a big help. I need one last thing. I've tried multiple COUNTIFs, COUNTIFS, and SUMPRODUCTS, but this is stumping me.

    I created a spreadsheet to automatically calculate the complexity of various tasks based on specific criteria, which is working fine. For background, the Client0000X sheets automatically calculate a project as Simple, Medium, or Complex in cell K5 based on the number of simple, medium, or complex tasks checked off in Columns C, E, and G. I want the Statistics sheet to automatically update as new Client0000X sheets are added to reflect the number of Simple, Medium, or Complex projects I've completed. One of the issues I'm running into is that I'm needing to manually insert each sheet into the formula; this workbook will eventually contain hundreds of Client sheets, so this would be incredibly time consuming.

    What I want is:
    • Statistics!B7 to aggregate the number of times the value "Simple" appears in cell K5 across all other sheets, including new sheets as they are added
    • Statistics!B8 to aggregate the number of times the value "Medium" appears in cell K5 across all other sheets, including new sheets as they are added
    • Statistics!B9 to aggregate the number of times the value "Medium" appears in cell K5 across all other sheets, including new sheets as they are added

    Any help you can provide is extremely appreciated.


    The question above has been solved. Workbook has been updated and new question is:

    Background: This is a project tracker. The Client0000X sheets (X being a number) contains a series of tasks ranging from simple, medium, to complex. The result of checked tasks in Column C, E, and G are tallied in Column J. Depending on specific criteria, the sheet then spits out a "scope" (or total project complexity) in Cell K5. These numbers are aggregated in the Statistics!Column A.

    Issue: I want to break these statics down by product type. The product type (Type A1, Type A2, Type A3, Type B1) lives in Client0000X!M5. I manually select the product type using data validation, which is what I want. The issue is that I can't figure out how to make Excel automatically tally the sums and averages of corresponding complexities and types. So for example, I want to be able to quickly see how many simple tasks I did for all Type A1 products, and the average scope (or project complexity) for Product Type A1. To be more clear:
    • In Statistics!F2, I want a formula that will provide the sum of J2 across all Client0000X sheets when M5 = Type A1; in Statistics!D2, the sum of J3 across all Client0000X sheets when M5 = Type A1; in Statistics!D3, the sum of J4 across all Client0000X sheets when M5 = Type A1. Similarly, Statistics!H2, the sum of J2 across all Client0000X sheets when M5 = Type A2, and so on. The goal here is to see the total number of simple/medium/complex tasks per product type.
    • In Statistics!F6, I want a formula that will provide the average of J2 across all Client0000X sheets when M5 = Type A1; in Statistics!D2, the average of J3 across all Client0000X sheets when M5 = Type A1; in Statistics!D3, the average of J4 across all Client0000X sheets when M5 = Type A1. Similarly, Statistics!H2, the average of J2 across all Client0000X sheets when M5 = Type A2, and so on. The goal here is to see the average number of simple/medium/complex tasks per product type.
    • In Statistics!F10, I want a formula that will look at every Client0000X sheet and count the number of times K5 = Simple and M5 = Type A1; Statistics!F11 should count the number of times K5 = Medium and M5 = Type A1, Statistics!F12 should count the number of times K5 = Complex and M5 = Type A1. Similarly, Statistics!H10 should count the number of types K5 = Simple and M5 = Type A2, and so on for the various product types and complexities. The goal here is to be able to see total number of simple/medium/complex project complexities per product type.

    Is this even possible? Any help you're able to provide is much appreciated.
    Attached Files Attached Files
    Last edited by newandnotverygood; 03-09-2022 at 02:19 PM. Reason: Replaced with new question

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,780

    Re: Calculate number of times a text value appears in a specific cell across sheets

    One possibility,
    - On each client sheet, add a helper column L2:L4 (hidden?) to flag if J2:J4 is greater than zero. (=(J2>0)*1)
    - Create a new blank sheet named "Last" as the last tab. Hide the sheet
    - On the client sheets
    Range B2:B4 use:
    Please Login or Register  to view this content.
    B7:B9
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,081

    Re: Calculate number of times a text value appears in a specific cell across sheets

    Pl see file.
    Enter all sheet names in Column M

    In B7 copy down.

    =SUMPRODUCT(COUNTIF(INDIRECT("'"&INDIRECT("M2:M"&COUNTA($M$2:$M$100)+1)&"'!K5"),A7))
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 03-09-2022 at 12:34 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    03-07-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    6

    Re: Calculate number of times a text value appears in a specific cell across sheets

    Thank you!

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    Re: Calculate number of times a text value appears in a specific cell across sheets

    based on CLOSED post

    in F2

    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!M5"),"Type A1",INDIRECT("'"&Sheets&"'!J2")))

    in F6

    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!M5"),"Type A1",INDIRECT("'"&Sheets&"'!J2"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!M5"),"Type A1")))

    You realise that all these results are "transient" as a change in "Type" in any sheet will immediately change these results so they are only a "Snapshot in time".
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  6. #6
    Registered User
    Join Date
    03-07-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    6

    Re: Calculate number of times a text value appears in a specific cell across sheets

    Quote Originally Posted by JohnTopley View Post
    based on CLOSED post

    in F2

    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!M5"),"Type A1",INDIRECT("'"&Sheets&"'!J2")))

    in F6

    =SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!M5"),"Type A1",INDIRECT("'"&Sheets&"'!J2"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!M5"),"Type A1")))

    You realise that all these results are "transient" as a change in "Type" in any sheet will immediately change these results so they are only a "Snapshot in time".
    Hi John,

    Thanks for your answer! The "Type" will not change, so this is fine. The formula, however, is returning #NAME?. Do I need to update it in any way to add the sheet names? If so, is there a way to do that where I will not need to update the formula every time a new sheet is added?

    TIA!

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    Re: Calculate number of times a text value appears in a specific cell across sheets

    Sorry - "Sheets" is named range of your sheets.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    03-07-2022
    Location
    USA
    MS-Off Ver
    365
    Posts
    6

    Re: Calculate number of times a text value appears in a specific cell across sheets

    Thanks, I really am quite new


    I tried applying your formulas but I'm running into a few issues:

    • Statistics!K2:K4 aren't pulling properly; it should be K2 = 1, K2 = 2, K2 = 3 (i.e., pulling number of simple/medium/complex tasks for all "Complex" scope projects across all sheets. In the attached, Client00005 is the only "Complex" scope project.)

    • Statistics!H6:H8 and Statistics!L6:8 are returning #DIV/0 errors; any idea why?

    • Any thoughts on calculating scope across all sheets? (E.g., Statistics!F10 should pull number of K5 cells across all sheets that say "Simple" if M5 = Type A1.)

    Thanks again!
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Aggregate statistics across multiple sheets if multiple critereon met

    As to the question about K2:K4 try pasting the following modification of the formula into cell L2 and then dragging the fill handle down to cell L4:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count number of times a specific text appears for a certain date
    By Alfie092 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-10-2020, 12:25 PM
  2. Counting number of times a value appears in a certain cell over multiple sheets
    By cdhampshire in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2018, 08:33 AM
  3. I need to calculate how many times a number appears on the sheet
    By topdealz in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-01-2015, 07:32 AM
  4. Replies: 1
    Last Post: 07-18-2013, 10:11 AM
  5. count the number of times a number appears in a specific range
    By myjebay1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2013, 11:01 AM
  6. Count the number of times a specific text appears in column D
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2011, 02:02 PM
  7. Counting the number of times a specific character appears in a cell
    By PCLIVE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-04-2005, 01:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1