each assay volume for month

16th??? What is that? What is the Excel PRODUCT you are using? O365, Excel 2021, Excel 2019, or what??

OK. This is a work in progress.

With the filters applied in individual sheets, this has the potential to be messy.

Another complicating factor is the identity of the analyses offered is not the same in both sheets.

Task 1 was to assemble a full list of lab/analyte combinations (even those that are not necessarily offered in a particular lab). If other analytes are offered in other labs, this may need to be tweaked.

Task 2 was to assemble the totals. To do this I changed the headers in the summary to match the tab names.

For 365, I used 3 formulae. No copy/pasting. Just 3 formulae in the yellow cells.

NOW you can start filtering out analytes/labs based on whatever you want.

Lab/analye combinations (all of them):

Formula:

Please Login or Register  to view this content.

Monthly totals:

Formula:

Please Login or Register  to view this content.

Overall totals:

Formula:

Please Login or Register  to view this content.

Oh, and please amend your profile to show O365, otherwise you'll be pestered every time you post here about the "16th"

In the (rather likely) event that the formula in C3 is VERY slow... replace it with:

=IFERROR(MAP(INDEX($A3#,,1),INDEX($A3#,,2),LAMBDA(y,z,SUMIFS(INDIRECT("'"&C2&"'!C:C"),INDIRECT("'"&C2&"'!A:A"),y,INDIRECT("'"&C2&"'!B:B"),z))),"")

and drag across (and ONLY across - not down) to Dec 2023

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

I am posting the link because you only have 5 posts:

https://chandoo.org/forum/threads/wo...linic-a.56428/

Undeclared crossposting. This REALLY annoys me.

No need to delete them.

Just show us some respect and tell us that you've cross-posted. That way we don't waste ages develping a silution to a solved problem.

Originally Posted by Sthomas11

sorry, have deleted the cross post, my apologies.

No, you haven't - it's still there. You won't be able to delete it, but that's not the point. You can cross post if you wish as long as you declare it as per this and the other forums' rules.

So, did either of the suggested solutions work for you?

Seems that OP is well aware of Xpost rules... https://www.excelforum.com/excel-gen...ml#post5721580

Thanks - in the light of this, I shall serve an infraction. The OP needs to understand that any further infringement will lead to a ban from this forum.

Stan - please read the message in the infraction notice carefully. Also, pleae update your forum profile to Excel 365 - 16th is misleading.