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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.