count between dates

[Sal_m_78]

Hi,

I have a list Open Dates and Closed Dates. What im am trying to achieve is between those dates how many times a 10 day marker was hit, a 20 day, a 25 day and a 30+ days, before the call got closed. If there is no closing dates than nothing will be counted. So in my summary i will have the months listed and than going across the top the headngs 10,20,25,30+ and than beneath that the number of calls that reached the 10,20,25,30+ day period.

Ive attached an example of what im trying to achieve, although its no where near to complete, in Cell F7 i have attempted something but its no where close. Any hint or a point in the right direction would be appreciated

Thanks

Sal

[zbor]

Try this:

example(1).xls

[Sal_m_78]

damn dude that was fast, i wasnt expecting you to do it for me, but thank you kindly. I may post again asking for an explanation of some of the formulas you have done, let me see if i can figure it out frist.

Thanks

Sal

[zbor]

Actually, now when I looking maybe it doesn't working because I wasn't sure do you want to calculate days upto 10 or not.

This one don't.

Also, you don't need two formula's.
Mine in E does same as your's in C.
So you can sligtly change some parameters and you get only one column (with letters for example).

Just write here ranges you want to have:

A......0-10
B......11-20
C......21-25
D......26-30
E......31+

Right?

Compare your values with A (in C column) and my values (in E column) with 0 and tell what's correct among those two? are days up to 10 calculated or not?

If you don't want to calculate dates up to 10 days difference and still want them to be marked as A thatn you need two columns and sheet is OK.

[Sal_m_78]

ok i think i got what you have done, do you mind breaking this formula down for me, ive been going through it and i understand some it, but do u mind just explaining it to me so i can be sure.

=SUMPRODUCT(--($A$17:$A$9999>=$A2),--($A$17:$A$9999>=$A3),--($E$17:$E$9999=COLUMN(A1)))

Thanks

Sal

[zbor]

Sorry, it's been wrong

--($A$17:$A$9999<$A3)

So it can look for current month. Greater or equal than 1.x.2009 and smaler then 1.x+1.2009

And I'll try to find some better explanation on forum then I can give you and give you link.

[Sal_m_78]

Actually, now when I looking maybe it doesn't working because I wasn't sure do you want to calculate days upto 10 or not.

This one don't.

Also, you don't need two formula's.
Mine in E does same as your's in C.
So you can sligtly change some parameters and you get only one column (with letters for example).

Just write here ranges you want to have:

A......0-10
B......11-20
C......21-25
D......26-30
E......31+

Right?

Compare your values with A (in C column) and my values (in E column) with 0 and tell what's correct among those two? are days up to 10 calculated or not?

If you don't want to calculate dates up to 10 days difference and still want them to be marked as A thatn you need two columns and sheet is OK.

No i only wanted to calculate on the 10 day mark,20,25,30+. From what i understand this is what the sheet does. Originally i was going to do 0-10, but the specification changed.
Thanks
Sal

[Sal_m_78]

Sorry, it's been wrong

--($A$17:$A$9999<$A3)

So it can look for current month. Greater or equal than 1.x.2009 and smaler then 1.x+1.2009

And I'll try to find some better explanation on forum then I can give you and give you link.

Thats ok, thank you for that. One more question what do the "--" stand for?

Thanks

Sal

[zbor]

This is final now with all corrections:

example(1)(1).xls

Also, A is for 0-9 days, B is for 10-19 days, etc

About -- you could find explained here:

http://www.excelforum.com/excel-2007...of-values.html