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

Sumif function calculation

0

Hi Team,

Please assist to calculate

1. The sumif function of a % from a range of number if they are actual

file attached.

Thanks

excelformulas-functions
posted by: siehartley2001 (rep: 14) on Sun Jun 6, 2021 at 9:34 am
Answer
Discuss

Discussion

Thanks John, this is helpful.
siehartley2001 (rep: 14) Jun 6, '21 at 1:37 pm
Good try Isaac but I meant you should use the Discussion under the Answer (and the Discussion button there) rather than under the question. Sorry if I confused you. 
John_Ru (rep: 6142) Jun 6, '21 at 1:42 pm
Add to Discussion

Answers

0
Selected Answer

Isaac

In cell C4, try this instead:

=IF($F$2="Actuals",SUM(F4:Q4)/SUM($F$17:$Q$17),"-")
then copy down (noting the $ signs fix the reference cell, sum rows). You may need to increase the number of decimal places to see the smaller values and/or change the FALSE response in the IF statement (from "-" to whatever you prefer).

Hope this helps.

posted by: John_Ru (rep: 6142) on Sun Jun 6, 2021 at 10:18 am
Discuss

Discussion

Hi john,
This formular is great for giving me the actuals for the whole year but how can we twick it to account for YTD. So from April to August I can have the % actuals of the total? Thanks
siehartley2001 (rep: 14) Jun 12, '21 at 2:10 pm
Isaac

Assuming column F is April (from the 4 in F3) and J is August, then the tweak for YTD in C4 would be (changes in bold):
=IF($F$2="Actuals",SUM(F4:J4)/SUM($F$17:$J$17),"-")
John_Ru (rep: 6142) Jun 12, '21 at 4:59 pm
Hi John,
Thanks for coming back to me- Thats means every month I have to extend the range to fetch the actuals to arrive at the % of actuals YTD, right?
siehartley2001 (rep: 14) Jun 13, '21 at 9:23 am
Isaac

Depends how you populate your table I guess... 
John_Ru (rep: 6142) Jun 13, '21 at 11:16 am
Add to Discussion
0

Thanks John, this is helpful.

posted by: siehartley2001 (rep: 14) on Sun Jun 6, 2021 at 11:48 am
Discuss

Discussion

Thanks Isaac but please use the Discussion (under an Answer) to make such comments on future. The Answer button is used when you want to provide a solution (which you can do for your own questions I believe).
John_Ru (rep: 6142) Jun 6, '21 at 12:07 pm
Add to Discussion


Answer the Question

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