convert Sumifs to sumproduct or similar

Hi Everyone

This is my very first post here and I'm wondering if you could help me? I have lost 2 days so far trying to figure this out.

I have this formula that does exactly what I want it to do =SUMIFS('2021'!$AD$15:$AD$493,Dates,">="&F$2,Dates,"<"&EDATE(F$2,1),WalletType, "="&$C4) except when I use the auto filters which I need to manipulate the figures but the values do not adjust. (Dates named range 2021'!$F$15:$F$500) (WalletType named range 2021'!$Ac$15:$Ac$500)

I believe I need to use something similar =SUMPRODUCT(SUBTOTAL(9,OFFSET(2021'!$F$15,ROW(2021'!$F$15:$F$500)-ROW(2021'!$F$15),0))*(2021'!$F$15:$F$500>=$G$10)*(2021'!$F$15:$F$500,<=$G$11)*(2021'!$AD$15:$AD$500)*(2021'!$Ac$15:$Ac$500=c12) but cannot make it work.

I am trying to produce a stock plan for stock items that are going to be). So have created a quick example document to assist my explanation the formulas above are not taken from the example just to clarify

The items are added to the project projections tabs on a year by year basis this will show all the history over time without the list becoming too huge and giving us useful history information. The date is added for when the items are required and the product is selected from a drop down list and a quantity placed in the quantity box next to it.
On the stock requirements tab I want to see the items for each product type by month. The production description on this page also works as a drop-down list for the other tabs. Via a data validation list
I need to collate the totals for each product for the month and show them in the stock requirement form
My intention is to use the orange year drop down box to be able to change the overview to look at the different years.
I need to use data filters on the project projection tabs to manipulate the information on the stock requirements tab.

the SUBTOTAL/OFFSET approach isn't great, from a performance angle, so I would suggest that instead, for ultimate flexibility, you add a new field to your source to flag row visibility, e.g

=SUBTOTAL(2,[Dates])
this will return 1 if the row is visible (via filtering), 0 if filtered out

you can then add this new field to your SUMIFS with a fixed criteria of 1 -- this will ensure you only ever include visible rows

Hi XLent
Thanks for this suggestion do you mean like this?

=SUBTOTAL(2,[Dates] ('2021'!$AD$15:$AD$493,Dates,">="&F$2,Dates,"<"&EDATE(F$2,1),WalletType, "="&$C4)

Hi John

Thanks so very much for the updated example. I missed that to start with. If I filter the project projections 2021 to exclude "Wallet Clear Plastic -B/fly/A7 Slot" the total amount still remains under January. This needs to only report on what is visible in the data filters (I did have to remove the blue line to make the auto filter work but that was me not thinking.)

I hope you understand what I mean.

many thanks

Stephen

Hi John

Thanks so very much for the updated example. I missed that to start with. If I filter the project projections 2021 to exclude "Wallet Clear Plastic -B/fly/A7 Slot" the total amount still remains under January. This needs to only report on what is visible in the data filters (I did have to remove the blue line to make the auto filter work but that was me not thinking.)

I hope you understand what I mean.

many thanks

Stephen

Originally Posted by Stephen Reeves

Hi XLent, Thanks for this suggestion do you mean like this? =SUBTOTAL(2,[Dates] ('2021'!$AD$15:$AD$493,Dates,">="&F$2,Dates,"<"&EDATE(F$2,1),WalletType, "="&$C4)

Hi, no; the SUBTOTAL field would reside on your Data tabs, directly -- that 0/1 field would then be referenced in your various SUMIFS with a fixed criteria of 1 (visible)

Refer attached for working example -- note, the file has 3 tables, 2020 + 2021 and then a further table which = either 2020 or 2021 based on selection thereby avoiding need for INDIRECT {volatile, like OFFSET approach}

hopefully this gives you some idea.... the results will adjust relative to year, and filter applied on source data tab

Thanks XLent

This does exactly what I need it to do. The only problem I have now is if I convert my current spreadsheets to Tables it seems to break other things

is it possible to do the same but without making tables?

Thanks

Stephen

...is it possible to do the same but without making tables?

Yes, I just used Tables for sake of expediency -- if you were to highlight both Tables in my earlier attachment and "Convert to Range" you will find the master table simply references the now defunct table ranges explicitly
(obviously you can have non-volatile dynamic names if you need them to expand/contract as you add data, akin to Table behaviour)

I fully accept @JohnTopley's note regards prudent use of Volatiles however, IME on this board (over the years) I've seen enough examples of people complaining of calc overhead to advocate non-volatile alternatives wherever feasible - esp. as we only have a limited idea of the calc tree dependencies etc; the use of the SUBTOTAL field on each source range is always worthwhile as it greatly simplifies the calculation, irrespective of volatility concerns.

Thank you Xlent and JohnTopley for your help with this. You have managed to solve my issue, I'm really pleased with the final result!

Regards

Stephen

Thank you, I've Marked the Thread as Solved