CODEWITHSUNDEEP
excelformulacountif
Andres Martinez
Andres Martinez

Reputation: 65

How to simplify multiple COUNTIFS in excel with non-consecutive ranges

I have a spreadsheet...

Excel SpreadSheet

A you can see, the cell F2 has a formula with multiple COUNTSIF, basicly checks the cells F14, F33, F62 y there is a Pass there, and if there is one will give you a % completion. My question is that I have to add around 20-30 COUNTIFS to that formula, is there a way to simplify it.

=(COUNTIF(F14,"Pass")+COUNTIF(F33,"Pass")+COUNTIF(F62,"Pass")+COUNTIF(F75,"Pass")+COUNTIF(F88,"Pass")+COUNTIF(F105,"Pass"))/(COUNTIFS(F14,"<>na")+COUNTIFS(F33,"<>na")+COUNTIFS(F62,"<>na")++COUNTIFS(F75,"<>na")++COUNTIFS(F88,"<>na")+COUNTIFS(F105,"<>na"))

This is not the final formula, still missing around 20 entries. If you're wondering why not do a simple F15:FXX, because i just need the cells that have a test case name, like F14, F33, etc.

Upvotes: 0

Views: 262

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

For the first part of your formula, you can use the INDEX function to return a non-contiguous set of values, which you can test.

For example, the equivalent for the first part would be:

=SUM(N(INDEX($F:$F,IF(1,N({14,33,62,75,88,105})))="Pass"))

The IF(1,N({…})) part is how you specify which cells (rows) in Column F to return.

Without knowing more about your data, not sure about handling the percentage issue.

Your posted formula would not calculate a percentage, as it is only dividing the SUM by whether or not F14<>"na" is true, and then adding one for the <>"na" factor for the rest

In earlier versions of Excel, you may need to confirm this array formula, hold down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula seen in the formula bar.

If you want to return the percent "pass" in your list of cells, merely divide the SUM by the number of cells. You can either hard-code that number, or compute it with something like:

 COLUMNS({14,33,62,75,88,105})

Or all together:

=SUM(N(INDEX($F:$F,IF(1,N({14,33,62,75,88,105})))="Pass"))/6

or

=SUM(N(INDEX($F:$F,IF(1,N({14,33,62,75,88,105})))="Pass"))/COLUMNS({14,33,62,75,88,105})

Upvotes: 1

Related Questions