Sum based on multiple criteria and date range

My data is thus:

Sales Data

Date Sales Person Charge Received
1/24/17 Bob $100 $100
1/31/17 Bob $200 $50
2/12/17 Bob $100 $75
3/1/17 Mike $300 $200
3/2/17 Mike $100 $100
3/15/17 Bob $200 $100

I want to import the above data every week from an external source: it will be in the above format everytime, but I don't know how many total rows there will be...it depends on how many sales were made. After I import it, I'd like a worksheet to automatically update output something that looks like:

Total Sales

BOB

JAN $300 $150
FEB $100 $ 75
MAR $200 $100


MIKE

JAN $0 $0
FEB $0 $0
MAR $400 $300



Basically, I want a tidy report that says "For all January Dates, for Bob, the sum of the first column is this and the sum of the second column is this". And "For all January Dates, for Mike, the sum of the first column is this and the sum of the second column is this"-- changing the month and the sales person when I need to.

I've run a pivot table 1,000 ways and can never get it to report on date range correctly. I can VIEW it (sort it) by date range, but can't get the data to flow over into a report that summarizes the info. If I sort it by "JAN", then it'll sum each sales person's data for only JAN, but I can't pull that figure into another cell in a report because as soon as I resort it, that cell changes. If I could use a pivot table, that would be great, but just using formulas to pull the info I need is fine as well. Any help you could provide would be fantastic. My brain hurts from trying. Also keep in mind my actual data is about 7,000 dates worth of sales across many sales people.

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I would also use a pivot table here. Highlight columns A:D > Insert > PivotTable > Put it where you want

Drag "Sales Person" then"Date" under Row Labels.

Drag "Charge" then "Received" under Values. If applicable, change Count to Sum through Value Field Settings.

Right-click on any date > Group > Months > OK

Now all that you have to do after new data is added is refresh the pivot table.

Originally Posted by oeldere

this is a perfect job for a pivot table.

My biggest problem with pivot table is that I can FILTER values based on dates, and then see the sum of the data that meets the criteria, but can't pull that filtered data out onto a report because when I change the filters to look at a different date range, the sums change. There is no one cell that I can point to that says "that cell is the sum of
Column C for Bob for Jan". I can "see" those results, but those results can't be referenced for other purposes. It's the date that messes it up: a pivot table would work perfectly if I wanted to see all the "Bob sales" or all the "Mike sales", but for all the "Bob AND January" sales, it just doesn't work. There is an extra step there. The pivot table can sort out a bunch of data, but can't change my list of dates in format xx/xx/xxxx to "January Sales". When I run a pivot table, it ends up listing all the "Bob Sales from 1/1/2017 and then all the Bob sales from 1/2/17, and then all the Bob Sales from 1/3/17, without ever a sum of just all Jan Bob Sales.

I'll try both the pivot table and SUMIF strategies above and report back. Can't thank you enough for the suggestions so quickly (too quickly! I won't be back at my data set until evening)

Attached sample file...

Originally Posted by 63falcondude

I would also use a pivot table here. Highlight columns A:D > Insert > PivotTable > Put it where you want

Drag "Sales Person" then"Date" under Row Labels.

Drag "Charge" then "Received" under Values. If applicable, change Count to Sum through Value Field Settings.

Right-click on any date > Group > Months > OK

Now all that you have to do after new data is added is refresh the pivot table.

This worked perfectly. I had no idea you could "group months"...perfect. Thank you! Reputation added.

Originally Posted by JohnTopley

If you don't want Pivot table which is the easiest solution (though I am not PT fan!) try this approach

=SUMIFS(Sum_Range,Range1,"=" &Salesman,Range2,">=" & start_date,Range2,",<=" & end_date)

Sum_Range=Charge

Range1= range with Salesperson

Range2= Range with your dates

start_date=cell with start date e.g, 1st Jan 2017

End_date= cell with end date31st jan 2017 (for example)

Use cells for start_end and End_dates rather than hard-coding them in formula

e.g. with input data on sheet "Data" and output on a new sheet

=SUMIFS(Data!C:C,DATA!A:A,A3,Data!B:B,">=" &B2,DATA! B:B, "<=" & B3)

on output sheet

List your Salespersons in a column (say A) starting row 3

Have Month Dates in Columns B onward

so row 2 is start date, row 3 is end_date (B2=01/01/2017) , B3=31/01/2017 or EOMONTH(B2,0)

SUMIFS in B3 down

Crazy! Worked as well! For some reason I also enjoy unnecessarily complicated formulas throughout instead of clicking a few buttons on a pivot table. It is kind of set it and forget it once it is done. Thank you!

Originally Posted by CaptainO

This worked perfectly. I had no idea you could "group months"...perfect. Thank you! Reputation added.

Happy to help! Thanks for the rep.