Need to count # entries between 2 dates and not 'closed'

[niccyka]

Hi all,
I am looking at a spreadsheet with 3 columns: Issue, DateRaised, Status.

I need to find the following where status <> "closed":

Current issues (less than 30 days)
Issues older than 30 days (less than 60 days)
Issues older than 60 days (less than 90 days)
Issues older than 90 days

I have tried COUNTIFS but can only seem to get issues where <> closed and current.

The formula is something like:

=COUNTIFS($C:$C,"<> closed",$B:$B,">" & TODAY()-30)

I am struggling to find issues over 30 days and less than 60 days as another piece of the puzzle needs to be put in but I cannot get my head around it.

Does anyone have any ideas to help me?

Thanks
N

 

[Sal Paradise]

=SUMPRODUCT(($B:$B < TODAY()-30)*($C:$C < > "closed"))
=SUMPRODUCT(($B:$B > TODAY()-30)*($B:$B < TODAY()-60)*($C:$C < > "closed"))
=SUMPRODUCT(($B:$B > TODAY()-60)*($B:$B < TODAY()-90)*($C:$C < > "closed"))

etc.

(Bear in mind that as you wrote it, you will be missing any issue that is 30 days old, or 60 days old, etc. -- you should make one of the lesser than or greater thans "equal to" as well)

 

[niccyka]

Thanks Sal!

The first formula works a treat.

My formula includes the >= for the first criteria on the other formula - but for some reason will not return anthing more than '0'.

I am expecting values (if I filter the records I get records returned).

Do you have any other ideas please?

Also - does it make a difference the fact I am referring to data on a separate tab within the same workbook?

N

 

[barry houdini]

I think Sal has got some of the < signs the wrong way round, a date can't be both greater than today-30 and less than today - 60....

You can still use COUNTIFS with the same logic, e.g. between 30 and 60 days old

=COUNTIFS($C:$C,"<>closed",$B:$B,"<=" & TODAY()-30,$B:$B,">" & TODAY()-60)

 

[niccyka]

Thanks Barry! That seems to work (formulas on same tab as data at the moment).

Just another quick question.... rather than working on 30, 60, 90 days etc - how could I get the formula to look for 'months' - ie; month -1, month - 2 etc...

 

[barry houdini]

Do you mean calendar months? You could use EOMONTH to get dates within last month (September at the moment), e.g.

=COUNTIFS($C:$C,"<>closed",$B:$B,"<="&EOMONTH(TODAY(),-1),$B:$B,">" &EOMONTH(TODAY(),-2))

 

[niccyka]

Thanks - will give that a go.

 

[Sal Paradise]

I think Sal has got some of the < signs the wrong way round, a date can't be both greater than today-30 and less than today - 60....

You can still use COUNTIFS with the same logic, e.g. between 30 and 60 days old

=COUNTIFS($C:$C,"<>closed",$B:$B,"<=" & TODAY()-30,$B:$B,">" & TODAY()-60)

I am nothing if not backwards.