Excel - COUNTIF not working w/ cells result of SUM and VLOOKUP

Asked By VettRace on 21-Jun-07 12:38 PM
I have a column which I am trying to get the number of occurrances of "9" for
example. The "9" is a formula of two functions 1) VLOOKUP in another column
and 2) SUM of various VLOOKUP columns.  I can't get COUNTIF to work on either
of these columns.  I think it doesn't like SUM because it is a result of
VLOOKUP columns.

Topper replied on 21-Jun-07 12:42 PM
What is (are) your formula(e)?
VettRace replied on 21-Jun-07 12:48 PM
=SUM(G51+J51+L51+N51+P51)/5  this is a result of various similar formulas

Peo Sjoblom replied on 21-Jun-07 12:49 PM
Shouldn't you post the formula that did not work?


Peo Sjoblom
Topper replied on 21-Jun-07 12:59 PM
When you say COUNTIF doesn't work, what results do you get? And your COUNTIF
formula is ..?

The fact the a value is derived from a formula shouldn't make any
difference? Do you always have valid values in the column(s) you are checking?
VettRace replied on 21-Jun-07 01:00 PM
"Peo Sjoblom" wrote:
VettRace replied on 21-Jun-07 01:31 PM
Sorry--here's the formula.
=COUNTIF('OPRS Conv Tracker'!Q51:Q200,9)
Which refers to the SUM formula mentioned which that refers to the VLOOKUP.
Even when I've changed it so it doesn't reference a another worksheet, it
still doesn't work.
Peo Sjoblom replied on 21-Jun-07 01:38 PM
When you say it is not working, do you mean you get an unexpected result or
do you get an error message?


Peo Sjoblom
VettRace replied on 21-Jun-07 01:43 PM
I get an unexpected result: an incorrect number.
Peo Sjoblom replied on 21-Jun-07 02:04 PM
Does it work if you use the column/range where the VLOOKUP pulls from?

My guess is that you somehow are not using the correct sheet/range or that
the values are not integers but that they have decimals as well

You can also try

=COUNTIF('OPRS Conv Tracker'!Q51:Q200,"*9*")

Shouldn't really matter and this is just to test if you will get anything at

Try this, select part of the range that you know has a 9, copy it to a new
sheet and paste special as values, wide the column if necessary and if you
see things like


then that is the reason, if so you might want to use round in the
vlookup/sum formulas

You can also try it in the new sheet, if you paste then as values into
A1:A100 or something use



Peo Sjoblom