Excel VBA Course
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.)

View Course

Need updated total revenue using SUMIF formula

0

Respected Members,

In attached sheet, I am using SUMIF formula to attain total revenue as per the name selected in drop down list. However, total revenue is being calculated as the same for all the employee names (whom so ever I select).

Please help ASAP.

Regards,

Akash Sharma

excel
posted by: Akash Sharma (rep: 40) on Wed Nov 4, 2020 at 1:31 pm
Answer
Discuss

Answers

0
Selected Answer

Hi Akash

Your formula in cell J3 is "=SUMIF(B2:B17,$B2,E2:E17)" but should read 

=SUMIF(B2:B17,$I3,E2:E17)
. (Your version summed values in E2:E17 that match B3 "Akash" so changing the drop-down in I3 didn't alter the sum in J3). I've done that in the attached file.

Also the dropdown in I3 could be simplified if you did data validation based on a separate list (without potentially many duplicates). In the attached I've illustrated  that in $J$9:$J$17 and applied the same data validation in I3 to B2:B17 too.

REVSION NOTE: The attached file now has my data validation list in alphabetical order (followed by potential new employees) for ease of selection. Also corrected cell references in second paragraph.

posted by: John_Ru (rep: 6142) on Wed Nov 4, 2020 at 2:23 pm
Discuss


Answer the Question

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