Hi All,
I'm trying to create a formula that allows the range values to be dynamic based on a MATCH formula returning a value.
Here is my example:
=COUNTIF(A1:A&(MATCH("NAME",B:B,0)),"countifcriteria")
I would like the range for the COUNTIF to change so it could be A1:A10 or A1:A1000 depending on the result of the MATCH formula.
Any help would be great as I can't think of a way around this one.
Last edited by thejrose; 08-23-2017 at 10:01 AM. Reason: Increase relevance of title
Try
=COUNTIF(A1:INDEX(A:A,MATCH("NAME",B:B,0)),"countifcriteria")
Hi Jonmo1,
Thanks for the reply.
The reason I had used just MATCH is that I want the range reference to be the same of the row of the matching criteria.
Using INDEX/MATCH wouldn't give me that.
Did you Try it?
Yes, but the INDEX would return a value in column A, other than the row of the criteria.
Another option would be to add an additional column of data with numbers incrementing for each row?
No, the Index returns a Range Object.
If "Name" is found in say B20, then it goes like this
=COUNTIF(A1:INDEX(A:A,MATCH("NAME",B:B,0)),"countifcriteria")
=COUNTIF(A1:INDEX(A:A,20),"countifcriteria")
A20 is the 20th cell in A:A, so Index returns the Range Object A20
=COUNTIF(A1:A20,"countifcriteria")
Last edited by Jonmo1; 08-23-2017 at 10:50 AM.
Meant to say A20 there
A20 is the 20th cell in A:A, so Index returns the Range Object A20
That's now working!
Thank you for your help.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks