CODEWITHSUNDEEP
excel-formulacountif
Alfric
Alfric

Reputation: 35

Aging formula without using additional column

I have a table like below

ID  StarDate    EndDate
123 6/5/2015 15:54  6/12/2015 15:54
124 6/6/2015 15:54  6/8/2015 15:54
125 6/7/2015 15:54  6/9/2015 15:54
126 6/8/2015 15:54  6/11/2015 15:54
127 6/9/2015 15:54  6/15/2015 15:54

I want to get the total count of aging without using any additional column
0-30 =COUNTIFS(A:A,">124",A:A,"<127",TODAY()-B2:B6,"<=30")

The one above is not working Please help achieving this.

Thankyou

Upvotes: 0

Views: 170

Answers (1)

teylyn
teylyn

Reputation: 35990

If your table starts in cell A1, with the first start date in cell B2, try

=SUMPRODUCT(--(C2:C6-B2:B6<=30))

Edit after clarification:

=SUMPRODUCT(--(A2:A6>124),--(A2:A6<127),--(TODAY()-B2:B6<=30))

Upvotes: 0

Related Questions