How to determine the number of rows that match a certain criteria

  • Thread starter Thread starter Mystery Man
  • Start date Start date
M

Mystery Man

I have a spreadsheet that contains a variety of software bugs. Each
bug has a priority, ie high, medium and low. What I want is to be able
to get the number of bugs for each priority, and as a percentage of
the total.

For example

A B
1 program crashes when selecting employee 123 - high
2 cumbersome way to add payments - medium
3 tab order on form ppp incorrect - low
4 cannot logon for user XYZ - high
5 tab order on form qqq incorrect - low

Number of high priority bugs = 2 (40%)
Number of medium priority bugs = 1 (20%)
Number of low priority bugs = 2 (40%)

Total = 5

ie a query similar to the one below:

=SUM(A1:A5 where B = "high")
 
See if this works for you.

Enter in
C2 - HIGH
C3 - MEDIUM
C4 - LOW

In D2, enter this formula:

=COUNTIF($B$2:$B$100,C2)&" Which Is
"&ROUND(COUNTIF($B$2:$B$100,C2)/COUNTA($B$2:$B$100)%,1)&" Percent"

And copy down to D2 and D3.




I have a spreadsheet that contains a variety of software bugs. Each
bug has a priority, ie high, medium and low. What I want is to be able
to get the number of bugs for each priority, and as a percentage of
the total.

For example

A B
1 program crashes when selecting employee 123 - high
2 cumbersome way to add payments - medium
3 tab order on form ppp incorrect - low
4 cannot logon for user XYZ - high
5 tab order on form qqq incorrect - low

Number of high priority bugs = 2 (40%)
Number of medium priority bugs = 1 (20%)
Number of low priority bugs = 2 (40%)

Total = 5

ie a query similar to the one below:

=SUM(A1:A5 where B = "high")
 
Back
Top