Count cells with specific values

I want to generate some stats from the data I have in the log tab of the excel sheet.

Of the various rows that are available, I need to find out how many belong to each 'Type'. That is, how many are 'Bug', 'Not a Bug', 'Product Issue' etc.

I am able to achieve this using the following formula (this needs to be replicated for each 'Type') -

=COUNTIF(Log!E3:E8,"Not a Bug")

However, there is one more dimension to it. I also need to categorize them based on Open and Closed issues. The Status column is used to decide that. Anything which has the status of 'Open' and 'In Progress' is deemed 'Open', while everything else (like Fixed, Closed, Verified in UAT2 etc) is deemed 'Closed'.

This is the part that I am having trouble with. Using the below formula, I was hoping to find the count of rows which have type of 'Not a Bug' and status of 'Open' (which includes 'In Progress' as well) but it returns 0

=COUNTIFS(Log!E3:E8,"Not a Bug",Log!E3:E8,"Open",Log!E3:E8,"In Progress")

Can someone please help me correct this? I am using Excel 2010.

Thanks in anticipation.
Log.xlsx
LVL 1
ank5Asked:
Who is Participating?
 
Rory ArchibaldCommented:
In C8:
=SUM(COUNTIFS(Log!$E$3:$E$8,$B8,Log!$E$3:$E$8,{"Open","In Progress"}))
in D8:
=COUNTIF(Log!$E$3:$E$8,$B8)-C8

and fill in the rest of the columns.
0
 
Rodney EndrigaData AnalystCommented:
Hi ank5, the COUNTIFS formula requires each criteria expression to be TRUE in order to produce the results. In your example, "Not A Bug", "Open", AND "In Progress" have to exist in your specified range. If all 3 of those do not exist, it will give you 0 in the corresponding cell.

Using your sample file, if you change the formula to this:

=COUNTIFS(Log!E3:E7,"Not a Bug",Log!F3:F7,"closed")

You will see how the results change (note: This counts 2 rows). You may have to adjust your formula in this manner.
0
 
Rob HensonFinance AnalystCommented:
As Rodney was saying, the criteria in the formula are dealt with as an AND comparison. As you are looking at column E for "Open" OR "In Progress" then the TRUE status will not occur.

In addition you are looking in column E for all entries. The Open or In Progress are in column F.

To correct it would be:

=COUNTIFS(Log!E3:E8,"Not a Bug",Log!F3:F8,"Open")+COUNTIFS(Log!E3:E8,"Not a Bug",Log!F3:F8,"In Progress")

Which counts each of the Open or In Progress entries and adds them together.

Thanks
Rob H
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ank5Author Commented:
Thank you Rodney, Rory and Rob.

Rory, I tried your formula and it seems to be very close to what I wanted to achieve.

However, for some reason 'Open' issues are not giving a correct count. In the attached sheet, I was expecting count of 1 for 'Open'/'Product Issue' but the count shows 1 for 'Closed/Product Issue'. I think same would be the case for 'In progress' as well.
Log.xlsx
0
 
Rodney EndrigaData AnalystCommented:
You will have to adjust cell G7 in Sheet3 (using your updated Log.xlsx file):

=SUM(COUNTIFS(NPB_UAT2!$E$3:$E$5,$F7,NPB_UAT2!$F$3:$F$5,{"Open","In progress"}))

You will need to reference the proper COLUMNS (E & F) in your formula.
0
 
Saurabh Singh TeotiaCommented:
Alternatively you can use this formula as well to do what you are looking for...

=SUMPRODUCT((NPB_UAT2!$E$3:$E$11=Sheet3!F4)*((NPB_UAT2!$F$3:$F$11="Open")+(NPB_UAT2!$F$3:$F$11="In Progress")))

Saurabh...
0
 
ank5Author Commented:
sorry, my bad. I should have split the points but accepted only one solution. Can I re-do the points?
0
 
Rory ArchibaldCommented:
You can click the Request Attention link below your original post.
0
 
Rodney EndrigaData AnalystCommented:
Yes, I agree, ank5. Please SPLIT THE POINTS because Rory provided the necessary formula and I just called out the proper columns to use in the formula & specified some logic behind the formula. I do not deserve all the credit here, RORY IS AWESOME!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.