# Aggregate statistics across multiple sheets if multiple critereon met

1. ## 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

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.  Register To Reply

2. ## 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.``  Register To Reply

3. ## 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))  Register To Reply

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

Thank you!  Register To Reply

5. ## 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".  Register To Reply

6. ## Re: Calculate number of times a text value appears in a specific cell across sheets Originally Posted by JohnTopley 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!  Register To Reply

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

Sorry - "Sheets" is named range of your sheets.  Register To Reply

8. ## 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!  Register To Reply

9. ## 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:  `Please Login or Register  to view this content.`

Let us know if you have any questions.  Register To Reply

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