Here is my formula
IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100))))))
If its Apples, SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100)
It goes on through multiple checks.
If its non of them and its "(ALL)" then it will sum everything.
If, for some reason when the criteria is NOT ALL (this is from a pivot table filter label) it doesn't sum the section the pivot is filtered on. Apples, Organges.. etc...
Am I missing something?
Here is a cleaner view
IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),
IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),
IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),
IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),
IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100))))))
Thanks,
IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100))))))
If its Apples, SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100)
It goes on through multiple checks.
If its non of them and its "(ALL)" then it will sum everything.
If, for some reason when the criteria is NOT ALL (this is from a pivot table filter label) it doesn't sum the section the pivot is filtered on. Apples, Organges.. etc...
Am I missing something?
Here is a cleaner view
IF('Table'!$D$4="Apples",SUMIF($A$118:$A$213,'Table'!$D$1,$D$118:$D$213)*SUMIF($C$53:$C$100,AP3,$D$53:$D$100),
IF('Table'!$D$4="Orange",SUMIF($A$118:$A$213,'Table'!$D$1,$E$118:$E$213)*SUMIF($C$53:$C$100,AP3,$E$53:$E$100),
IF('Table'!$D$4="Bananna",SUMIF($A$118:$A$213,'Table'!$D$1,$F$118:$F$213)*SUMIF($C$53:$C$100,AP3,$F$53:$F$100),
IF('Table'!$D$4="Grape",SUMIF($A$118:$A$213,'Table'!$D$1,$G$118:$G$213)*SUMIF($C$53:$C$100,AP3,$G$53:$G$100),
IF('Table'!$D$4="(All)",SUMIF($A$118:$A$213,'Table'!$D$1,$H$118:$H$213)*SUMIF($C$53:$C$100,AP3,$H$53:$H$100))))))
Thanks,