Force formula built via concatenation to calculate

[Ken Soona]

Just like the title says.

I have a COUNTIFS formula I built in pieces and glued together using cell concatenation. The formula works just fine, but I need to click into each individual cell, and hit enter, for it to calculate. I have 16K cells. How do I force XL to look at it as a formula and calculate?

I know it's b/c XL thinks it's text, but I've tried changing the format of the cells, saving it, closing it, opening it, etc. Nothing makes it run.

Context:

I had to use an inequality in quotes as part of the formula, and the cell reference in quotes won't dynamically change as I copy down the column, so I had to built it via concat.

 

[Ken Soona]

EDIT

Change format to number or general, save as csv. Close. Open. Boom.

 

[jtakw]

Hi,

Can you show your formula?

Also, make sure you have Calculations set to Auto.

EDIT, looks like I posted just about the same time you did. Glad you got it working.

 

[Ken Soona]

Yes, I did. The formula wasn't the issue, it was that since I glued it together w concat, even after pasting 'just the value' XL knew it was pieced together n treated it like text.

Saving it as a csv worked b/c I assume csv doesn't deal with formatting, so it came back in as a csv file and it just did what it thought based on what was in the cell.

Thank you for your reply, though. I appreciate it.