COUNTIF with absolute column, relative row in Google Sheets

1
votes

I have this table and I want B1 and B2 to display the sum where D1, F1 and H1, and D2, F2 and H2 are bigger than 0, respectively. I will be inserting two columns to the left of C on a regular basis, and I don't want the column references in the formula to be updated when the columns are shifted, but if B2 becomes B3 when a row is added above it, I will need the formula to be updated for the row and check the values in D3, F3 and H3, or if I add rows below it, I want to be able to drag down and get the formula copied with incremental row values. I tried INDIRECT with and without &CELL("address", E2) but I couldn't get it to work as I wanted it.

  A      B (Sum)  C            D  E            F  G            H
1 Josh   3        Some number  6  Some number  4  Some number  3
2 Fiona  2        Some number  1  Some number  0  Some number  4
2
google-sheetsgoogle-sheets-formula
In B1 try: =SUMPRODUCT(ISEVEN(COLUMN(C1:H1))*(C1:H1>0)). I'm thinking it should be possible through QUERY too. – JvdV

2 Answers

0
votes

You can use OFFSET to get absolute column references, and COUNTIF to count if each referenced cell is bigger than 0:

=COUNTIF(OFFSET(B1,0,2),">0")+COUNTIF(OFFSET(B1,0,4),">0")+COUNTIF(OFFSET(B1,0,6),">0")
0
votes

Not perfect, but try =SUM(OFFSET(B2,0,1,1,500)). That way you refer to the cell itself so whenever you add columns it's not affected. I used 500 arbitrarily since it's simple. Adjust as needed.