Placing the a date value to a 3 different groups

[hellforce]

Hi Guys,

Im rather new to excel and currently using excel 2013.
I have a worksheet with column A ( data group) Column B ( Sent date with MM/DD/YY) , Column C Aged Group {using this formula =IF(B2="","",(TODAY()-B2))} , Column D status ( if the record is open or close).

I would like to create a table that can segregate the data group and how many days it has already aged. And then group them with different aged group. I would like to seek your help on the above scenario and do please refer to the attached screenshot.

From:-

Data Group Sent Date Aged Status
Quotation 06/06/14 35 Open
Invoice 07/11/14 0 Open
invoice 05/01/14 71 Open
PO 07/01/14 10 Open
PO 07/05/14 6 Open
SO 06/30/14 11 Open
Quotation 05/18/14 54 Open
SO 05/05/14 67 Open
Cash Note 07/03/14 8 Close

To:-

Data Group 0-15 15-30 30 above
Quotation
Invoice
PO
SO
Cash Note

[morevijay1982]

sir for your self

i workout and highlighted please go through it

[Saarang84]

Hi,

Welcome to the Forum

Find enclosed a more generalized version using Vijay's file..

[hellforce]

Hi morevijay1982 and Saarang84 thank you so much for the idea. I have modified it and put in a condition so i dont have to add another column. Thanks you so much for your quick reply and assistance :D you rock. Just to share what i modified, but i notice my formula do not have $ . Will it prompt any error?

0-15
=COUNTIFS(A2:A10,H2,C2:C10,"<=15")

16-30
=COUNTIFS(A2:A10,H2,C2:C10,">15",C2:C10,"<=30")

above
=COUNTIFS(A2:A10,H2,C2:C10,">30")
above 30

[oeldere]

Probably it will not give you an error.

You get a wrong answer.

explaination of the $

$A$2 will keep the row (2) and colum (A) the same if you copied or dragged the formula.

$A2 will change the row (2) to 3 if you drag the formula down / the column will NOT change, so it will use column A.

A$2 will change the colum (A) in B if you drag the formula to the right / the row (2) will NOT chang, so it will use row 2.

Hope I explained well enough, so you understand why the formula will give an wrong answer.

[kvsrinivasamurthy]

Pl see attached file with formulas.

[hellforce]

oeldere , thanks for the explanation it helps.
kvsrinivasamurthy, i learned alot from you sheet. THANK YOU !

Would like to express my gratitude from the bottom of my heart.
THANKS guys for the assistance and guidance.