SOLVED

Creating a COUNTIF formula that always references one cell to the left

Copper Contributor

Hello! 

 

I'm working on a project that involves using a COUNTIF statement which will always reference the cell directly to the left of it. I'm trying to figure out a way to automate the process, because I have hundreds of these unique values that occur thousands of times throughout the sheet, but I don't want to have to spend the time to plug in a specific reference for each value. I'm hoping someone can help out!

 

Thanks in advance, 

Clyde

3 Replies
Would =+COUNTIF(A:A,B1) not work if B1 is the cell left of your countif and A:A is the data range? We need some more info on the structure of your data.
Thanks, but I actually just figured it out!
best response confirmed by allyreckerman (Microsoft)
Solution
So, I've answered my own question,
What I ended up doing was creating an OFFSET statement within the COUNTIF function,

=COUNTIF(A1:A10000, (OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)))
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution
So, I've answered my own question,
What I ended up doing was creating an OFFSET statement within the COUNTIF function,

=COUNTIF(A1:A10000, (OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),0,-1)))

View solution in original post