How to do sum calculation for filtered cell only by using offset function

0

Refer to column I, I need to do the manual formula to get the Accumulated Sale% once i filter Column F. So,  is there any automated formula can be applied in Column I once i do the filtering?

Answer
Discuss

Discussion

Please don't forget to select the answer that worked best for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1482) Sep 1, '17 at 8:17 am
Add to Discussion

Answers

0

Please try this formula.

=SUMIF($F$2:$F2,$F2,$G$2:$G2)/$G$40

It will give the correct result for each of the filtered items alone but not when several items are shown simultaneously. The following formula suppresses a wrong result.

=IF(SUMIF($F$2:$F$39,$F2,$G$2:$G$39)=$G$40,SUMIF($F$2:$F2,$F2,$G$2:$G2)/$G$40,"")

As you see, the formula checks if G40 holds the total for a single filtered item and displays nothing if more than one item is displayed. The idea might be extended to show the correct result for all possible combinations but the programming would be quite voluminous, the more so the more items there are.

Either formula can be copied down to all visible and invisible cells.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login