silverback24
Board Regular
- Joined
- Jul 30, 2013
- Messages
- 58
I made a pseudo pivot table with dynamic date ranges, salesmen, and account fields. This works great for them. Now, they want me to add their forecasting data to this. The problem is their forecasting data is set up in a table with units and revenue given per month. I need a formula that works similar to my nested if(sumifs) formulas in that if no salesman or account info it totals all for the time period entered. Then, if a salesman, account, or both is entered, would sum the forecasted units and revenue for the date range that fits the extra criteria. These are also broken down into 18 categories.
Snip of layout:
-- removed inline image ---
Formula for part that works:=IFERROR(IF(AND(ISBLANK($F$6),ISBLANK($F$5)),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$D:$D,Dashboard!$E9),IF(ISBLANK(Dashboard!$F$5),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$G:$G,Dashboard!$F$6,Data!$D:$D,Dashboard!$E9),IF(ISBLANK(Dashboard!$F$6),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$A:$A,Dashboard!$F$5,Data!$D:$D,Dashboard!$E9),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$A:$A,Dashboard!$F$5,Data!$G:$G,Dashboard!$F$6,Data!$D:$D,Dashboard!$E9)))),"Check Date Range")
Sample data for forecast:
Picture didn't work, turned to a wall of text.
Layout
A B C D E F-Q
Account Name Cat Salesman empty Months as 1/1/2014, 2/1/2014,...
Snip of layout:
-- removed inline image ---
Formula for part that works:=IFERROR(IF(AND(ISBLANK($F$6),ISBLANK($F$5)),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$D:$D,Dashboard!$E9),IF(ISBLANK(Dashboard!$F$5),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$G:$G,Dashboard!$F$6,Data!$D:$D,Dashboard!$E9),IF(ISBLANK(Dashboard!$F$6),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$A:$A,Dashboard!$F$5,Data!$D:$D,Dashboard!$E9),SUMIFS(Data!$H:$H,Data!$C:$C,">="&Dashboard!$F$3,Data!$C:$C,"<="&Dashboard!$F$4,Data!$A:$A,Dashboard!$F$5,Data!$G:$G,Dashboard!$F$6,Data!$D:$D,Dashboard!$E9)))),"Check Date Range")
Sample data for forecast:
Picture didn't work, turned to a wall of text.
Layout
A B C D E F-Q
Account Name Cat Salesman empty Months as 1/1/2014, 2/1/2014,...
Last edited: