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

 

[kellywy]

Sounds good to me! And bring your dog!!

 

[FDibbins]

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

I have mocked up some data to see if this will help you?
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td]bb[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]aa bb 2000-2010[/td][td]aa[/td][td]

3​

[/td][td]COUNTIF(A2:A4,"*bb*")[/td][/tr]

[tr][td]

3​

[/td][td]aa 2000-2010 bb[/td][td]bb[/td][td]

3​

[/td][td]COUNTIF(A2:A4,"*"&C1&"*")[/td][/tr]

[tr][td]

4​

[/td][td]aa bb 2000-2010 cc dd[/td][td]aa[/td][td]

2​

[/td][td]COUNTIFS(A2:A4,"*bb*",$B$2:$B$4,"aa")[/td][/tr]
[/table]
I did it 2 (well, 3) different ways, with the formulas shown in column D
1st 2 are the same, differing only in either had-coding "bb" or referencing it
2nd 1 uses 2 count criteria

 

[kellywy]

Hiya! Okay - could be me again. (in fact, that's always the safe bet, I've discovered)

So, how would I enter the column I want to count -- if I need to do it two ways:

Example #1 : Count the cells in Column J that have ANYthing (text/number/date) in them, for each sales agent (in Column F).
Example #2 : Count the cells in Column H that have specific text (i.e., "Sale", "No Sale", etc.) - for each sales agent in Col. F.

How's that for being a pain?

Kelly

 

[FDibbins]

Sounds good to me! And bring your dog!!

Wish I could, had her from 6 weeks until 16 years, no longer with me for 2 years now

 

[kellywy]

Awwww. I'm sorry to hear that. That's the part about dogs that sucks. Been through it many times myself.

 

[FDibbins]

Maybe something like this?
[Table="width:, class:grid"][tr][td] [/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td][/td][td][/td][td]anything[/td][td]sale[/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]aa[/td][td][/td][td][/td][td]blah[/td][td]sale[/td][td]

1​

[/td][td]COUNTIFS($F$2:$F$13,"aa",I2:I13,"")[/td][/tr]

[tr][td]

3​

[/td][td]bb[/td][td][/td][td][/td][td]

123​

[/td][td]no sale[/td][td]

2​

[/td][td]COUNTIFS($F$2:$F$13,"aa",J2:J13,"sale")[/td][/tr]

[tr][td]

4​

[/td][td]cc[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]aa[/td][td][/td][td][/td][td]blah[/td][td]sale[/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]bb[/td][td][/td][td][/td][td]blah[/td][td]sale[/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]cc[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]aa[/td][td][/td][td][/td][td]

123​

[/td][td]no sale[/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]bb[/td][td][/td][td][/td][td]

456​

[/td][td]no sale[/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]cc[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]aa[/td][td][/td][td][/td][td][/td][td]no sale[/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]bb[/td][td][/td][td][/td][td]

333​

[/td][td]sale[/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]cc[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Sorry the formula in K2 is wrong, it is counting the "nothings", it should be
=COUNTIFS($F$2:$F$13,"aa",I2:I13,"<>")

 

[kellywy]

PERFECT!!! Got it all in there and everything's working great!

Thank you so much, Ford! You are absolutely my New Best Friend!

Kelly

 

[FDibbins]

Im just happy I could help, and it is always a pleasure helping someone as appreciative as you

I await your next damsel-in-distress call