asked on

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

Rodney Endriga

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.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

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

ank5

ASKER

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

SOLUTION

Rodney Endriga

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saurabh Singh Teotia

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

ank5

ASKER

sorry, my bad. I should have split the points but accepted only one solution. Can I re-do the points?

Rory Archibald

You can click the Request Attention link below your original post.

Rodney Endriga

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