Multiple COUNTIF Criteria

Hi All,

Hoping somebody can help me with a formula I need in Excel 2013.

I have a number of accounts and need to provide some basic MI, one piece of information I need is the number of cases that were registered in a particular month.

So I have a column for Date Received which is I3 and then I need a COUNTIF that picks up the number of cases within a given month but for three separate criteria's (received, open, resolved). For example I have Feb cases, I need a formula to pick up how many cases received between 1st - 28/29th Feb that are currently resolved and then a separate formula to pick up the same info but if the criteria is open, the data for whether the case is open or resolved is in column AN3.

Also is it possible for me to highlight/count any that fall outside of an SLA, so for example I have 300 cases, an SLA to resolve them within 10 days, so a formula that picks up from date received whether the case is outside of SLA.

Kind Regards

Hi
Can you upload a sample file showing the results you expected?

Excel Help.xlsx

So for example in this example, I have all the dates that each case was received in column D and the outcome in column E

In cell J4 I want a formula that picks up first how many cases were received in Feb but then how many of them Feb cases were resolved and then similar in K4 but for open if it makes sense, the answer would be Feb 3 cases resolved, 3 open.

But then I also want something if it's possible that picks up the date received so for example 1st Feb, I'm currently on 20th Feb and my SLA to resolve was 10 working day, is there a formula I can use to highlight all the cases still within 10 working days SLA and then how many are not?

Regards

In J4

=SUMPRODUCT(--(MONTH($D$4:$D$12)=2)*($E$4:$E$12=$J$3))

J3="Resolved"

in K4

=SUMPRODUCT(--(MONTH($D$4:$D$12)=2)*($E$4:$E$12=$K$3))

k3="Open"

Change 2 to 3 and 4 for March/April

in G4

=IF(AND(D4+10<$G$1,E4="Open"),"Outstanding","")

G1=20/02/2016 (your "Today")

Copy down