Calc'ing Sales Totals by Date Range and Sales Agent

[kellywy]

Hi all! As usual, under the gun and spending time searching for this solution, but not finding it so I'm back to bothering y'all!

I'm trying to figure out how to do the following:

a) Total one column of sales figures. (H7:H60)
b) Based on two different criteria:
----The first is based on a date range (E7:E60 has specific dates entered - 9/1/17, 9/5/17, etc.). I need to pull the sales figures for each month.
----The second is based on the salesperson's initial (F7:F60)


I keep getting tripped up on how to account for the dates - so that I pull all dates >=9/1/17, and all dates <=9/30/17 (and so on). (Probably because I have the retention capacity of a slug today.)

Any suggestions? I will love you forever and promise not to bother you more than once a day.

Thanks!

Kelly

 

[FDibbins]

maybe something like this?

=sumifS(H7:H60,E7:E60,">="&cell-with-start-date,E7:E60,"<"&edate(cell-with-start-date,1),F7:F60,"name")

If you put the start date and name in their own cells (maybe even use a dropdown), it will make the formula more dynamic

 

[kellywy]

Hey, Ford! Thanks for checking in on this!!

I've been trying to make that work, but I keep getting an error that won't allow me to put it in. (even gone through the "insert formula" - but no luck.
Here's what I'm trying to enter - maybe you can tell me where I'm screwed up? (Oh great - it just let me put it in, but now it doesn't calculate anything - just turns into 0.)

=SUMIFS(H7:H60,E7:E60,">=A74",E7:E60,"<=B74,F7:F60="EL")

I put the start and end dates of each month into A74 and B74.
The EL is the sales agent initials.

And it still hates me! :=(

 

[kellywy]

Ahhhhhh! Just got it! You rock, Ford. Always my hero!!!! Thank you so much!

 

[FDibbins]

Thanks for the kind words Kelly, always a pleasure to help you

Just out of curiosity, what where you doing wrong to begin with?

wait, I think I see what you did...
=SUMIFS(H7:H60,E7:E60,">=A74",E7:E60,"<=B74,F7:F60="EL")
should be...
=SUMIFS(H7:H60,E7:E60,">="&A74,E7:E60,"<="&B74,F7:F60="EL")
or could also be...
=SUMIFS(H7:H60,E7:E60,">="&A74,E7:E60,"<"&edate(a74,1),F7:F60="EL")
That way, you only need 1 date (provided you only want 1 month)
If you need more than 1 month, put the ,1) from EdATE in it's own cell so you can specify how many months you need

 

[kellywy]

My eyeballs just glazed right over those ampersands and the edate....ha! But strangely, it didn't help do go through the formula builder, either.

I knew if I focused I'd figure out my error.....because YOU are always my Prince! Thanks again!!

Kelly

 

[FDibbins]

aaww shucks, fanx

easy way to approach that kinda thing (using a simplified version of your formula)...
if you use just a number
=SUMIFS(H7:H60,E7:E60,10)
sum all items in H where the cell in E = 10

if you use just text
=SUMIFS(H7:H60,E7:E60,"ten")
sum all items in H where the cell in E = Ten

if you use just a specific cell reference
=SUMIFS(H7:H60,E7:E60,A1)
sum all items in H where the cell in E = whatever is in A1

if you need to add < a certain value
=SUMIFS(H7:H60,E7:E60,"<10")
sum all items in H where the cell in E < 10

This is where it gets a bit tricky
if you need to add < a certain value in CELL ref
=SUMIFS(H7:H60,E7:E60,"<"&A1)
sum all items in H where the cell in E < the value in A1
Note that now you need to include the & to combine the 2 arguments. This is how excel deals with combining text (the < sign is text) and a cell ref

Hope that didn't confuse you even more?

 

[kellywy]

Hahahahahahaha! (<----me, being hysterical) You know what? I ALWAYS save your formulas and messages in my own little "figure out how to use Excel b the time you die, Kelly" file. But some days, it takes a bit more concentrated effort than others. (This is one of those days....!)

You're the best............

Kelly

 

[kellywy]

Case in point: I have officially lost the last of my marbles today.

On that same spreadsheet, I'm trying to do a count of a column with text....based on the Sales Agent. And what I thought would be nice and easy now has me pulling my hair out. (you're welcome for the image)

Count? CountIf? If? KillMyself? I can't find anything to work!

I need to count boxes with text in I7:I60 - that have "EL" in F7:F60.
Gah! (no drama here.......)

 

[FDibbins]

...You know what? I ALWAYS save your formulas and messages in my own little "figure out how to use Excel b the time you die, Kelly" file....

You just made my da, it's comments like that, that keep bringing me (and others) back here to help and share our little bit of knowledge
Next time I am in Vegas (well, never been there before, so...when), I will have to look you up