Stuck with an IF or THEN or AND or VLookup or COUNT or SUM --- ANY HELP

[jmclozano]

HELP -- Racking my head on this one.

I need to pull $ value based on a date range for a specif month

Last day of the month 1/31/2012
calculate
Beyond 60 days (between)
Beyond 90 days (between)
120 days (before or =)

Got this part =COUNTIF(payments!E:E,">=" &(E79-90)) - COUNTIF(payments!E:E,">" &E79-60) and then Sum =SUMIF(payments!E:E,">=" & E79-90,payments!H:H)-SUMIF(payments!E:E,">"& E79-60,payments!H:H)

THIS IS MY ISSUES

But I also need it based on a filed in COLUMN AE which is PAY_END dates. I needs to pull date ranges i.e I only want rows from Feb 1-29, 2012 from this Column and then do the above Count and SUM formulas for the date ranges.

I used a pivot table but its too much click click click and three different tables.

Does it make sense?

1. Select rows form date range in Columne AE
2. Then Count the rows for the data range 60 day, 90 day 120 day
3. then SUM the value of sales for the date ranges 60, 90, 120

My summary I would like to look like this

Month Month Month
60 - $0.00 | #Items $0.00 | #Items $0.00 | #Items
90 - $0.00 | #Items $0.00 | #Items $0.00 | #Items
120 - $0.00 | #Items $0.00 | #Items $0.00 | #Items

[jmclozano]

ANY HELP IS APPRECIATED
I attached some sample data.

This is my formula in written form.

select all [pay end (AE)] rows that match [month input] then select rows to match if the last day of month is [insert date (6/30/12)] then calculate between 60 days from [out date (D)] and 90 days from [out date] then calculate the number of rows between those dates and total the [commission (F)]

select all [pay end (AE)] rows that match [month input] then select rows to match if the last day of month is [insert date (6/30/12)] then calculate between 90 days from [out date(D)] and 120 days from [out date] then calculate the number of rows between those dates and total the [commission (F)]

select all [pay end (AE)] rows that match [month input] then select rows to match if the last day of month is [insert date (6/30/12)] then calculate 120 days before or equal to [out date(D)] then calculate the number of rows between those dates and total the [commission (F)]

[loginjmor]

Hi -

Not sure if I fully understand what you are trying to get, but attached is a spreadsheet with my best stab at it. I think the SUMPRODUCT function is what you want to use.

Basically if you change the end of month date in cell B18 it recalculates the amount and number of commissions beyond 60 days, 90 days, and 120 days. The thing that I may not be understanding is when you get beyond 120 days, all three amounts will be the same as all accounts would be beyond 60,90, and 120 days. That's why I'm not sure this is what you want.

Anyway, take a look and let me know if you need anything else.

[jmclozano]

Thank you - I think we are almost there we just need the calculation of the between dates - then the sums would be different.
Thoughts - comments

[loginjmor]

OK - I think I get what your are looking for: In the beyond 60 days, you want anything greater than 60 days AND less than 90 days. For beyond 90 days you want anything greater than 90 days AND less than 120 day. The last entry is anything beyond 120 days.

Take a look at the attached spreadsheet and see if that works for you.