Formaula needed to Calculate orevious month revenue by name and date range

[Chykat2000]

$ value in column J
Accepted in column M
Date in column N
Sales Rep Name (3 different reps) in Column R

I need a formula based on the $ value ACCEPTED in the month of September and by sales rep name. Any help would be greatly appreciated.


Kat

[HangMan]

Hi Chykat2000,

Sounds as though a PivotTable would be the simplest option, can you upload a sample workbook?

[Chykat2000]

There are lots of elements for this workbook. I have a tab for every month and hidden tabs to calculate the formulas I need that feed into another spreadsheet. This is the only formula that I can't figure out
Book 1.xlsx

[HangMan]

Hi Kat,

Not sure if the attached is what you are looking for. I've simply added a PivotTable with a Date and a Status filter. If this doesn't give you what you need, let me know which cell/s on your data sheet you are looking to show these totals...

[Chykat2000]

I know how to do a pivot table. I need a formula that will calculate based on the $ value ACCEPTED in the month of September and by sales rep name. I have 20 airports that I have to compile data into 1 spreadsheet.

If Accepted (column M), Between 9/1-9/30 (Column N), Calculate the $ value (Column J) for specific rep name lets say Katrina (Column R)

[JohnTopley]

Try this formula


=SUMPRODUCT(($J$2:$J$100)*($M$2:$M$100="Accepted")*(MONTH($N$2:$N$100)=9)*($R$2:$R$100=R2))

R2="KATRINA TAYLOR"

Create list of Sales Rep say AA2:AA20 , formula in AB2 ,and replace R2 with AA2 and copy down.

[HangMan]

In which case try:

Please Login or Register  to view this content.

and adjust the range according to the number of rows you have... I've restricted it to Rows 2 to 82 because this is the limit of the data in your sheet and because you have totals at the bottom of column J.

Let me know if that works for you?

[Chykat2000]

The sumifs formula worked perfectly, thank you so much