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?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How to do sum calculation for filtered cell only by using offset function
0
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: 1989) Sep 1, '17 at 8:17 am
Answers
0
Please try this formula.
=SUMIF($F$2:$F2,$F2,$G$2:$G2)/$G$40It 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.