Excel: parse text as formula

Question

I would like to have part of an excel formula be dynamic, other than a cell reference.

For instance, suppose that in column A (cells A1:A99) I have a bunch of numbers, and I want to know how many of those numbers are greater than 50.

If I wanted this calculation to be static, I could simply use one of the following:

=COUNTIF($A$1:$A$99,">50")
=SUM(IF($A$1:$A$99>50,1,0))
=SUM(($A$1:$A$99>50)*1)

I mention all three because my actual formula is hairy and a bit of a mix of the second and the third. (After all, perhaps something will work with COUNTIF but not with the others.)

Now, I want to be able to type my condition in another cell (say C1). So if I type ">50" in C1, my calculation will be as above, but if I type "<100" I will count how many entries of column A are less than 100.

Is this possible? (I am using Excel 2003 on Windows XP.)

Answer 1

There may be something that I'm missing. If you give

=COUNTIF($A$1:$A$99,C1)

in any cell, and then in cell C1 you type >50 or <100 don't you get what you want?

Answer 2

Use INDIRECT

=INDIRECT(COUNTIF($A$1:$A$99,">50"))

is same as

=COUNTIF($A$1:$A$99,">50")

But, as you identified, the former, you can generate within the excel cells! I do it all the time, for such things.

Answer 3

I usually solve this by adding another column carrying the result of a complex logical expression, like

=AND(OR(C3<D3;E3>=100);A3=VLOOKUP(B3;Sheet2!$A$2:$B$212;2;FALSE))

this formula is in all rows of -say- column F - note: no IF needed here!

then I calculate a complex conditional sum across column E using =SUMIF() like

=SUMIF(F2:F57;TRUE;E2:E57)

I know that some users say "I do not want to change my design. That's ok; my argument is that I have better control over the condition, I can work on the condition seperately from summing up or other functions that rely on that condition, and I can filter records for TRUE or FALSE to look at the subsets and have a rapid overview if the formula makes sense

hope that helps Good luck MikeD