CODEWITHSUNDEEP
excelformula
David Redford
David Redford

Reputation: 3

Using COUNTIF, OFFSET, MATCH in Excel

I keep getting an error around this certain part of my COUNTIF function and cannot find out why. I believe it's cause the Offset function won't output a range. It seems to work fine if I manually put a range, but that isn't an option.

How do I get a range as an output using Match?

=COUNTIFS(OFFSET(Sheet2!$A$1,0,MATCH(I$1,Sheet2!1:1,0)),"*Accountable*")

Upvotes: 0

Views: 3540

Answers (2)

David Redford
David Redford

Reputation: 3

Solved:

A.S.H's tip on keeping the ranges the same solved most of it. The second bit I was running into was just a miscalculation of columns. It needed to be the match function and then minus 1 column.

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

I think you want to count "Accountable" from $A$1 until the found match, so you are trying to "expand" the cell A1 by as many cells. The parameter to enlarge the number of columns in the OFFSET function is parameter 5. Try this:

=COUNTIFS(OFFSET(Sheet2!$A$1,0,0,1,MATCH(I$1,Sheet2!1:1,0)),"Accountable")
'                              ^^^

You could use INDEX to achieve the same. (sometimes preferred for its non-volatility):

=COUNTIFS(Sheet2!$A$1:INDEX(Sheet2!1:1,MATCH(I$1,Sheet2!1:1,0)),"Accountable")

Upvotes: 1

Related Questions