# 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")

Thanks in anticipation.
Log.xlsx
LVL 1
###### Who is Participating?

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.

Data 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
Commented:
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

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

Finance 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
Author 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
Data 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
Commented:
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
Author Commented:
sorry, my bad. I should have split the points but accepted only one solution. Can I re-do the points?
0
Commented: