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
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