Year to Date Sales Computation Assistance Request

Administrative Note

Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. Thanks.

YTD

in CJ5

=SUMIFS($C$5:$CH$5,$C4:$CH4,"A2021")

in CK5

=SUMIFS($C$5:$CH$5,$C$4:$CH$4,"T2021")*(MONTH(TODAY())-1)/12

Proportioned according to months elapsed as monthly targets are the same value


Better to change dates in row 3 to Excel dates (01/01/2021 etc) and format as "mmmm"

Format in Sheet2 better and easier for computations.

So how do you want target calculated: you have the same values values in the target months so changing A2021 has no effect on the formula I provided (which could be (is probably) an incorrect one)

Typical target calculation is initially divide YEAR target by 12 (so each month is the same) and in subsequent months subtract ACTUALS and divide by remaining months to get new monthly targets.

I see no initial YEAR target

Hi John, again my thanks for your assistance. What I meant was I wanted the YTD Target (T2021) be the same with Actual Sales (A2021), which means if the available Actual Sales is only until September (YTD Sales = Jan-September) the Target should also sum only until September (YTD Target = Jan-September), at least I am reading performance apple to apple which is YTD Sales and YTD Target automatically without having the sum them every month manually as I do now because the account list goes all the way to 2000 plus accounts.

By having it automated (auto computed) I dont need to manually change the formula monthly 2000++ times, but i will only put Sales on the current month and it will compare YTD Sales vs YTD Target.

YTD is Year to Date.

Thanks.

Francis

The formula I gave summed the data in columns headed T202! until September.

The attached has the merged cells removed (should be avoided like the plague) and this formula in YTD T2021

in CK5

=SUMIFS($C$5:$CH$5,$C$4:$CH$4,"T2021",C3:CH3,"<" & EOMONTH(TODAY(),-1))

If you want actual sales

=SUMIFS($C$5:$CH$5,$C$4:$CH$4,"A2021",C3:CH3,"<" & EOMONTH(TODAY(),-1))

Hi John,
thank you your help is much appreciated. try putting sales under October A2021, you will see that column CK5 (YTD T2021) doesnt increase which is suppose to add the target under October (colum BP 5 - A2020).

I manually checked by adding it manually, YTD Target adding October should be 1,450,386 but it remained the same at 1,111,104. Please see attached sheet with manual computation.

Apologies as I cant also figure out, I tried using sumproduct(isblank formula but it was limited to 2 columns I think or it be range. Im such a newbie in excel.

thanks

Francis

Try

=SUMIFS($C$5:$CH$5,$C$4:$CH$4,"T2021",$C5:$CH5,"<>")

which assumes cells are empty until required month is entered (rather than check against date).

You own figures in incorrect: for example, you include C5 rather than D5.

Hi John,

May bad on the error, I edited it and simplied everything simply using number 1 as place holder so that total sales end of year is 12, total target end of years is 12 as well as shown in the attached sheet. I edited and used the formula you shared its giving me the entire year target instead of the YTD Target.

Thank you.

Francis

Did you look at reply in post #12???

You have two options: you either sum based on a date (my first option where i used EOMONTH function)

or

sum based on all cells but leave (for example) November/December blank if you want to sum until October.

Both these options work.

Not sure that I totally understand, however the following formula in cell CK5 will display 10 when a number is typed into cell BN5:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.