0

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.

Improve this question
3
  • Can you show us a sample of your data. What is with the cells in between? Can "PASS" occur there? Commented Jul 10, 2020 at 12:27
  • Thanks for the reply.Actually I'm taking the data from the random cells.I can explain in detail if i can attach the excel sheet here,But I'm not sure how to attach the sheet here. Commented Jul 10, 2020 at 13:33
  • I just want to know, what the other cells around contain. If they will never contain "PASS", you could just use countif over the whole range. Just make a screenshot of a part of your data, if it is ok to show it. Commented Jul 10, 2020 at 16:26

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much Neizan. I tried Option 1 and it worked fine for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.