Counting Values Using Complex Criteria

Question

I have a workbook were I dump raw data exported from another system, and then use formulas to extract the data that interests me.  One of the columns is a status field, and I want to count up the number of items in alert.  To do this I use the following formula:

     =COUNTIF('Raw Data'!G$2:G$146,"alert stage 1")

I have similar formulas for alert stages 2 and 3, and deadline alerts. I know that I can use a wildcard to count up all alert items with a single formula, but I need to break them out and chart them by alert stage, hence the multiple formulas.

Now my boss has thrown a monkey wrench into the works.  He wants me to ignore lines for certain employees.  So, basically, count all alert stage items that don't belong to Bob, Betty, or Jane.

I have a table containing employee names and I could add columns to that table for each alert stage and a column to total the alert stage items.  But adding five columns to the table would have a major impact on the way I have my data laid out, so I'd need to rework the overall layout of my tables, which would be a pain.

So I am wondering if there is any way to do the following:

     =Countifs('Raw Data'!G$2:G$146,"alert stage 1",Employees_Table,User ID exists)

where User ID exists would be a test to find if the user ID exists in the Employees table.  This would allow me to count all the alert stage items for only those employees who are in the table.  Ignoring alert items for certain employees then becomes a simple matter of deleting them from the Employees table.

I think that I'm on the right track.  I just can't seem to find the right functions and syntax.  Is there a way to do this?

Thanks for any help that you can offer!

--Tom

Answer 1

None of the answers given here really helped, so I ended up going in a different direction.  I decided that my basic approach was flawed.  I had been trying to use a single formula that counted up all the rows in a range that met a compound criteria.  After failing to acheive that I decided that I needed a way to mark each line that needed to be counted, and each line that needed to be ignored.  Once marked, I could count up the rows in any way that I pleased.

Here's how I solved the problem.

1. I added a column to the tab were the raw data gets pasted.
2. I created a formula that does the following:
   • Matches the user ID from the same row to the Employees table.
   • If no match is found then the formula results in the word "Ignore."
   • If a match is found the formula takes the user ID and joins a two-character code at the end denoting the alert stage (s1=Stage 1, s2=Stage2, etc.)
3. On the tab that collects all the data for charting I created one formula that takes the user ID from the Employees table and matches it to the left-most 6 characters in the calculated column on the raw data tab, and that gives me the count by employee.  A set of formulas looks for the right-most 2 characters and that gives me the count by stage.  I don't need the count by employee for each stage, so the two separate counts are good enough for my purposes.

--Tom

Answer 2

I've not had a lot of experience with pivot tables, although I have working through a few online tutorials.  I'll give that a try.

However, I would nonetheless be interested in finding out if it can be done with a formula, if for no other reason than to see the syntax.  Here's some sample data from my Raw Data tab:

        G                    H             I

        Alert Status      Problem   Assignee

119   Deadline Alert  Test          User1

120   Alert Stage 3    Test         User1

121   Alert Stage 3    Test         User2

122   Alert Stage 3    Test         User3

The Assignee corresponds to an entry in the Employees table, which is on the same tab as the formula I'm trying create.  So say that I want to pick up alert stage 3 records for user1 and user3, but not user2 who does not appear in the Employees table.  Here's what I just tried:

=COUNTIFS('Raw Data'!G$119:G$122,"alert stage 3",'Raw Data'!K$119:K$122,MATCH('Raw Data'!K$119:K$122,A3:A16,0)>0)

The first part works to count the "alert stage 3" entries, and the MATCH command works when I put it in a cell by itself, but the two part joined together into this one formula do not work.  Maybe, because I'm dealing with ranges, it just can't be done in the way that I am thinking, but I'd be curious to know if it can.

--Tom

Answer 3

Depending on your data layout I would be tempted to just throw it into a Pivot Table, use your alert status for row labels (filter out any you don;t need, like blanks), count any field you like as long as you know it will always be populated (maybe alert status again, if the one with values in are all you care about).

Add employees to Report Filters and filter as you need.

You can chart this directly using a Pivot Chart (just insert a chart while you have the active cell in the Pivot Table), and all the filters etc will be used as you would expect. To chart it the way you want, you may have to switch rows for columns in the PT, but the concept remains the same

---

Adam Vero MCT, MMI, MBMSS:CRM 4, MCSA:Messaging Meteor IT - Training and Consulting Services

Answer 4

So say we just found

alert stage 1

in (say) G2, where do we look for the name of the owner of that record so we can test if the name exists in you data table of names?

 

 

=Countifs('Raw Data'!G$2:G$146,"alert stage 1",Employees_Table,User ID exists)

---

If this post answers your question, please mark it as the Answer.

Mike H

I thought about that last night *after* I turned off the machine! ;-)

The user ID exists in two places--'Raw Data'!K$2:K$146, and in Employees_Table on the same sheet as the Countifs formula.  So if "alert stage 1" is found in 'Raw Data'!G2, the user ID will be in 'Raw Data'!K2.  If I can then use that to do the second part of the formula then I think it'll work.

--Tom

Answer 5

Hi,

What your trying to do sounds simple enough but may  not be with your current data layout.  In order to evaluate the bold part of the formula below, it may be best done another way. We would ned to establish whoownsthe record we just counted in G2:G146. So say we just found

alert stage 1

in (say) G2, where do we look for the name of the owner of that record so we can test if the name exists in you data table of names?

=Countifs('Raw Data'!G$2:G$146,"alert stage 1",Employees_Table,User ID exists)

---

If this post answers your question, please mark it as the Answer.

Mike H