M melvin Member Feb 10, 2016 #1 Gday all, I use a combination of sumifs and sumproduct to apportion sales to stores, however I do one division and one status at a time. Is there a way where I can do sumifs and sumproducts all together to get the final result. Regards, Melv.
Gday all, I use a combination of sumifs and sumproduct to apportion sales to stores, however I do one division and one status at a time. Is there a way where I can do sumifs and sumproducts all together to get the final result. Regards, Melv.
Hui Excel Ninja Staff member Feb 10, 2016 #3 Wasn't this answered in: http://forum.chandoo.org/threads/fo...s-based-on-division-budget.25929/#post-157522
Wasn't this answered in: http://forum.chandoo.org/threads/fo...s-based-on-division-budget.25929/#post-157522
Hui Excel Ninja Staff member Feb 10, 2016 #4 X11: =W11+INDEX($U$3:$V$6,MATCH($V11,$T$3:$T$6,0),MATCH($U11,$U$2:$V$2,0))*$W11/(SUMPRODUCT(($U$11:$U$24=$U11)*($V$11:$V$24=$V11)*($W$11:$W$24))) Copy down
X11: =W11+INDEX($U$3:$V$6,MATCH($V11,$T$3:$T$6,0),MATCH($U11,$U$2:$V$2,0))*$W11/(SUMPRODUCT(($U$11:$U$24=$U11)*($V$11:$V$24=$V11)*($W$11:$W$24))) Copy down
M melvin Member Feb 10, 2016 #5 That was my first step. However wasn't getting the output. Appreciate your time mate. Have a great day Melv.
That was my first step. However wasn't getting the output. Appreciate your time mate. Have a great day Melv.
M melvin Member Feb 10, 2016 #7 Did exactly what I wanted mate. Couldn't have asked for more..lol.. Cheers mate Melv.