SUMPRODUCT with nested SUMIFS and range as criteria

[TG2812]

Hello,

I have the below formula which is working when I only have 1 range as criteria. As soon as I add a second range as criteria (to perform a subtotal - roll up of several product family), the formula works but the result it returns is wrong.

Formula working:
=IFERROR(SUMPRODUCT(SUMIFS(Sheet2!$F:$F,Sheet2!$C:$C,Sheet1!$A$26:$A$28,Sheet2!$D:$D,Sheet1!$B8,Sheet2!$G:$G,"<"&TODAY(),Sheet2!$G:$G,">="&DATE(YEAR(TODAY()),1,1)))/SUMPRODUCT(SUMIFS(Sheet2!$E:$E,Sheet2!$C:$C,Sheet1!$A$26:$A$28,Sheet2!$D:$D,Sheet1!$B8,Sheet2!$G:$G,"<"&TODAY(),Sheet2!$G:$G,">="&DATE(YEAR(TODAY()),1,1))),0)

Formula not working as soon as second range as criteria is inserted (in red bold):
=IFERROR(SUMPRODUCT(SUMIFS(Sheet2!$F:$F,Sheet2!$C:$C,Sheet1!$A$26:$A$28,Sheet2!$D:$D,Sheet1!$B$5:$B$8,Sheet2!$G:$G,"<"&TODAY(),Sheet2!$G:$G,">="&DATE(YEAR(TODAY()),1,1)))/SUMPRODUCT(SUMIFS(Sheet2!$E:$E,Sheet2!$C:$C,Sheet1!$A$26:$A$28,Sheet2!$D:$D,Sheet1!$B$5:$B$8,Sheet2!$G:$G,"<"&TODAY(),Sheet2!$G:$G,">="&DATE(YEAR(TODAY()),1,1))),0)

Any idea how I could possibly adjust the formula?

Thank you in advance for your time and great help

 

[Domenic]

That's probably because the range size is different for Sheet1!$A$26:$A$28 and Sheet1!$B$5:$B$8 ...

 

[TG2812]

@Domenic , thank you for your time and reply.

How can I modify the formula to deal with the different range size? I'm afraid I cannot change the ranges in my case.

 

[Domenic]

Try posting a small sample of the data, along with the actual expected result.

 

[TG2812]

Sheet1:

Sheet2 (I cannot include the entire data set)

Hope this helps clarify.

 

[Domenic]

Can you please copy/paste the data as text into your post, instead of providing an image? With images, we're not able to copy/paste into Excel for testing purposes. Also, for clarity, please confirm how you want the calculation to take place.

 

[TG2812]

Hi,

Basically the formula should do a division between column 2 and column 1 following a set of criteria.

• First criteria is the country (column C - Sheet2, criteria Sheet1 range A13:A15)
• Second criteria is the product family (column D - Sheet2, criteria Sheet2 range B5:B8)
• Third criteria is the date (less than today and equals to this year = 2019)

I'm sorry I can't find a quick and easy way to replicate the image in a simple format here..