CODEWITHSUNDEEP
excel-formula
abhi
abhi

Reputation: 368

How to combine multiple COUNTIF formula in excel

I'm creating a test summary sheet collecting data from multiple tabs in a single spreadsheet using COUNTIF-formula, eg:

=COUNTIF(OSPF!H3,"PASS")+COUNTIF(OSPF!H8,"PASS")+COUNTIF(OSPF!H13,"PASS").

Here data is collected from different cells(not continuous cellrange). Problem is, if the testcases list is big, then the COUNTIF-formula also will be large. Is there any option to simplify this formula. Thanks in advance.

Upvotes: 0

Views: 624

Answers (1)

neizan
neizan

Reputation: 2321

Option 1
One way to simplify it is to use just one COUNTIF function and combine all the cells into one noncontinguous range. With some help from this answer, I came up with the following for your case:

=SUM(COUNTIF(INDIRECT({"OSPF!H3","OSPF!H8","OSPF!H13"}),"PASS"))

Option 2
If you have control over what appears in each cell ("PASS"), then you could easily change "PASS" to "1" (if passing) or "0" (if failing). Then no COUNTIF would be necessary, just a simple SUM of all those cells instead.

Option 3
A variation of Option 2 would be to use a helper cell next to the cell that contains "PASS", which does the COUNTIF just for that single cell. Then in the "master" sheet, you just sum the helper cells that contain integers.

Upvotes: 1

Related Questions