Need 2 formula regarding daily sales goals & conditional date function

**THOMHJ** · 03-11-2016, 08:32 PM

Hi all,

I'm having some difficulties calculating these formulae.

The first issue I'm having is with "sumif("

I'm trying to get a certain column to calculate gross made if the date corresponds to the function.

The formula I'm using is "=sumif('NEW CAR'!A10:A180,"3/1/2016",'NEW CAR'!K10:M180)"

The sheet where the formula is needed is the 'CCH TOTAL' sheet, in the Front Gross column. I need the formula to correspond to the 'NEW CAR' sheet.

It's calculating it at $1,000. The proper sum is $2754.00.

Secondly, I'm trying to calculate the sales goal daily based on the number of working days remaining, and how many units already sold.

Basically, we are trying to hit 152 units sold, and we have 36 sold(broken down into 18 new units, 18 used units, and 4 CPO units.) The forecasted number for new, used, and CPO respectively is 65,75,12. I need a formula that will calculate the daily number of units we need to sell in order to hit each forecasted goal. I'm alright with Excel, but algebra was never my strong suit.

**alansidman** · 03-11-2016, 09:38 PM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

**THOMHJ** · 03-12-2016, 10:25 AM

fixed, thank you.

**Ochimus** · 03-12-2016, 08:55 PM

THOMHJ,

On the attached

1. CCH Total sheet:
Your request says that New Car sales on 1 March should total $2,754.00. But there aren't any on the spreadsheet you provided?

E37 gives the "Front Gross to date" (£19,859), shown by day in E4 - E34.

Daily formula is as follows:

=SUMIFS('NEW CAR'!K$9:K$28,'NEW CAR'!A$9:A$28,A4)+SUMIFS('NEW CAR'!L$9:L$28,'NEW CAR'!A$9:A$28,A4)+SUMIFS('NEW CAR'!M$9:M$28,'NEW CAR'!A$9:A$28,A4)

(Find all the rows in Col A of the 'New Car' sheet where the date matches the date in Col A of the 'CCH sheet', and add the numbers in Cols K, Col L and Col M of the 'New Car' sheet)

E39 then gives the Month to Date Average ($1,805.36):

=E37/(TODAY()-A4)

(Divide the Front Gross to Date in E37 by the number of days between today and the start of the month in A4)

2. Summary showing Daily Sales required

A34 has a DropList which is your deadline to make the sales.
B34 - D34 show the targets for each category (this is manual - if this is on the sheet somewhere, you can link to those cells)
B35 - D35 show your sales to date for each category. (Also manual - if this is on the sheet somewhere, you can link to those cells)
B36 - D36 show how many you have to sell each day to achieve the targets by the deadline

Formula as follows:

=IF(OR($A34="",B34="",B35=""),"",IF($A34<TODAY(),"Invalid Date",IF($A34=TODAY(),B34-B35,((B34-B35)/(MATCH($A34,Date,0)-MATCH(TODAY(),Date,0))))))

Daily target is blank if you have not set the target date, or entered a sales target, or entered anything in the sales to date (If you haven't sold anything yet, enter zero).
If the target date has passed, it says the target date is invalid.
If the deadline is today, it shows (target sales - sales to date), because you have to meet the target by the end of today!
If the deadline is in the future, it finds the number to sell (target - actual). and divides that by the number of days until the deadline (deadline - today)

Hope that helps?

Ochimus

**THOMHJ** · 03-12-2016, 10:11 PM

Thanks! I'll test it out tomorrow and let you know. Appreciate it!

Need 2 formula regarding daily sales goals & conditional date function

LinkBack

Thread Tools

Rate This Thread

Display

Need 2 formula regarding daily sales goals & conditional date function

Re: Need 2 separate formula

Re: Need 2 separate formula

Re: Need 2 formula regarding daily sales goals & conditional date function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Excel2010 IF Formula to return value based on a separate date and two separate text values

[SOLVED] Macro needed to Concatenate data in 2 separate tables of 2 separate sheets

[SOLVED] Need help to separate numeric values into separate cells (LEFT and RIGHT won't work)

[SOLVED] Breaking paragraph into separate phrases and putting each phrase in separate rows

Calculating time to close support cases, data in two separate sheets in separate rows

Macro to auto-email separate worksheets of same workbook to separate recipients?

[SOLVED] How do I separate words in one cell into two cells?-manually separate hundreds of nam

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions