CODEWITHSUNDEEP
excelexcel-formulaoffsetcountif
chicagobeast12
chicagobeast12

Reputation: 695

COUNTIFS and OFFSET

I have a dynamic table that starts at Row A5 (so I'm adding rows to the table constantly and the countif formula is located a couple of rows beneath the table in column C). 5 is the header row. Rows A1-A4 are commented. Data starts at A6. Having a little trouble with the height argument in my formula. I want to count number of rows with a value starting at A6 to the end of the table). Here's my current formula.

COUNTIF(OFFSET(A6,0,0,ROWS(A:A)-ROW(A6),1)"<>")

This works, however the height argument (ROWS(A:A)-ROW(A6)) makes no sense to me. I don't even know how I came up with it. If someone can explain or give me an alternative that would give me a range from A6 to the end of the worksheet, that would make sense, that would be great.

   A                  B        C
1  Comment
2  Comment
3  Comment
4  Comment
5  Table Header
6  "green"
7  "blue"
8  "red"
9  "blue
10  "yellow"
11
12                    Total    2

Upvotes: 0

Views: 1264

Answers (1)

Scott Craner
Scott Craner

Reputation: 152535

to count the negative we count the whole and subtract:

=COUNTA(A6:INDEX(A:A,MATCH("zzz",A:A)))-SUMPRODUCT(COUNTIFS(A6:INDEX(A:A,MATCH("zzz",A:A)),{"Blue","Yellow"}))

If the {"Blue","Yellow"} is are values in cells then:

=COUNTA(A6:INDEX(A:A,MATCH("zzz",A:A)))-SUMPRODUCT(COUNTIFS(A6:INDEX(A:A,MATCH("zzz",A:A)),Z1:Z2))

If the list of ignores can be expandable:

=COUNTA(A6:INDEX(A:A,MATCH("zzz",A:A)))-SUMPRODUCT(COUNTIFS(A6:INDEX(A:A,MATCH("zzz",A:A)),Z1:INDEX(Z:Z,MATCH("zzz",Z:Z))))

Upvotes: 1

Related Questions