##### 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.)

# Sumif function calculation

0

Hi Team,

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

file attached.

Thanks

### Discussion

siehartley2001 (rep: 10) 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: 4312) Jun 6, '21 at 1:42 pm

0

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.

### 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: 10) 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: 4312) 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: 10) Jun 13, '21 at 9:23 am
Isaac

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