WORKAROUND: turns out, COUNTIF ignores text cells, so I can just use a single range and ensure all comments are stored as text. However, if someone could answer this for cases where the comments might be numbers, I would be very appreciative.
In Excel: I would like to get a count of how many cells contain a given value, which (I assume) should be very simple using COUNTIF or COUNTIFS functions. However, my issue is that I would like to count across multiple non-continuous ranges (i.e. multiple non-adjacent columns in the same row) such that I get a count of all X-valued cells in that row.
The data I'm working with is set up with some columns being used for "score" and some for "comments", and I need to count the number of, say, scores of 1 in the row:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | score1 | comment1 | score2 | comment2 | extraneousCol | score3 | comment3 | count1 |
| 2 | 1 | Lorem | 2 | Ipsum | irrelevant | 1 | Dolor | 2 |
| 3 | 2 | Sit | 2 | Amet | impertinent | 0 | Consectetur | 0 |
| 4 | 1 | Adipiscing | 0 | Elit | inapplicable | 2 | Praesent | 1 |
The final column is what I would like the formula to output, i.e. outputting a count of how many cells have value 1 in the respective row. As you can see, there are also columns that break up the regularity of the score/comment pattern. These make it harder to automatically select ranges, as I can't simply select every 2nd cell.
Here's what I've tried:
=SUMPRODUCT(COUNTIF(INDIRECT({"A2","C2","F2"}),"=1"))usesINDIRECTto work around the one-range requirement ofCOUNTIF, andSUMPRODUCTto output a single value, but it's a pain to enter the cell locations with quotes inINDIRECT({...})=COUNTIFS(A2,"=1",C2,"=1",F2,"=1")works but is also a pain to input all the criteria
Maybe my problem is one of selecting multiple ranges, and not of counting across ranges? Let me know if I should move this question.
Edit: COUNTIF will ignore text cells, so in this case, I'm good to use a single range (i.e. the row) and single criterion, i.e. =COUNTIF(A2:G2,"=1").
CodePudding user response：
To make sure it counts correctly even if the comment would be 1 you could avoid COUNTIF and use SUMPRODUCT solely by creating an array of TRUE's in the header starting with the text score and multiply that array with TRUE's in the current row with value 1:
=SUMPRODUCT((LEFT($A$1:$G$1,5)="score")*(A2:G2=1))