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

### 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: 1382) Sep 1, '17 at 8:17 am

0

``=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.