sum countifs, multiple criteria AND a less than date

[Ricky Harding]

Hi,

I have a list of items, with various status (open, closed, in progress, accepted ...) I also have a ' Last Actioned' date.

I want to display a number which is a count of items with a subset of the status's (ie, "Open" and "In Progress" AND which have a last actioned date which is older than 6 months.

I can do the 'display a count of items with a given status', and I can do the 'display a count of those items whcih are 6 months old or older.' However, when I combine them using this:

=SUM(COUNTIFS(G12:G40,{"Open","In Progress","Mitigated","Accepted"},(E12:E40),"<="&TODAY()-180))

I just get the same result as =COUNTIF(E12:E40, "<="&TODAY()-180). Changing one of the status's to an excluded status, for example 'closed' doesn't alter the answer.

Any ideas on how best to acheive this? Thanks in advance.

 

[Roderick_E]

This sounds like a perfect job for a pivot table

 

[Ricky Harding]

You'd think so...

Unfortunately, I have merged cells, various other counts and some VB running to add new data and they stop a pivot table functioning. Also, a pivot table is overkill for what should be a one line function. Thanks though Roderick.