Excel help - Report data counts

[HungryHippos]

Hi guys, for my job I fill in an excel sheet with details of all the cases I deal with, basically it goes a bit like this:

column 1 | column 2 | column 3 | column 4

"case number" "case type" "escalation/resolved" "case category"

case number - simply a 5 digit number, this is always filled in if the row is for a case
case type - internal case or external case
escalation/resolved - states whether we escalate the case to another department or if we resolve the case
case category - the kind of case we are dealing with, we have a long list to choose from

Basically I want to be able to tally some results automatically, I want to create another sheet on the workbook, and in this sheet add some values that get automatically calculated

For example I would like to be able to count the number of external cases that get escalated to a specific department, or resolved etc

I can't think of a way to automate this off the top of my head, as you need to count if certain values are true across the row. so I want a formula that does something like this:

If Column 1 is not empty, if Column 2 is external case, and column 3 is resolved, and column 4 is category x then add the number of rows where this is true to get a total

Is this possible? any questions let me know

 

[Antar Bolaeisk]

You should be able to solve this using the =countifs statement

ie.

=countifs(sheet!B:A,"External",Sheet1!C:C,"Resolved",Sheet1!D, "Category x")

This should tell you how many external cases were resolved (assuming Case Type is column B, Escalation/Resolved is column C and Case Category is column D)

For watching which department external cases are escalated to it should just be a matter of replacing "resolved" in the formula with the appropriate identifier.

 

[Malakite]

You could use a sumproduct formula e.g.

=SUMPRODUCT(($B$1:$B$22="Internal")*($C$1:$C$22="resolve")*($D$1:$D$22="Category"))

Just specifing what you are looking for in each array. From this you could also replace the criteria with a cell reference. So instead of "Internal" point to cell A1 then it will look for whatever is in that cell in The Column B range you have specified.