COUNTIF not working w/ cells result of SUM and VLOOKUP

[VettRacer]

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.

 

[Toppers]

What is (are) your formula(e)?

 

[VettRacer]

=SUM(G51+J51+L51+N51+P51)/5 this is a result of various similar formulas

=VLOOKUP(O51,$V$7:$W$18,2)

 

[Peo Sjoblom]

Shouldn't you post the formula that didn't work?

 

[Toppers]

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?

 

[VettRacer]

=COUNTIF(Q51:Q56,9)

Shouldn't you post the formula that didn't work?

 

[VettRacer]

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]

When you say it is not working, do you mean you get an unexpected result or
do you get an error message?

 

[VettRacer]

I get an unexpected result: an incorrect number.

When you say it is not working, do you mean you get an unexpected result or
do you get an error message?

 

[Peo Sjoblom]

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
all..


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

9,000000001

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

=COUNTIF(A1:A100,9)

--
Regards,

Peo Sjoblom