# Thread: nested IF formula (2000)

1. ## nested IF formula (2000)

I'm trying to count number of occurences of a value based on 3 conditions, each condition being dependant on the previous. Here's what I have:

{=SUM(IF((range1="literal value")*(range2="literal value")*(range3="literal value"),1,0))}

The above formula works great and gives me the correct number of occurences. However, for maintenance purposes, I can't use the "literal value", but instead need to point to a cell reference for each of the 3 conditions. When I change the literal value to a cell reference, the formula returns a 0 instead of the correct number.

Any idea why this formula won't work with a cell reference?

2. ## Re: nested IF formula (2000)

Hi,
There's no logical reason that that formula wouldn't work with a cell reference instead of a literal. Are you definitely comparing like with like - i.e. not comparing numbers with text or anything. (note: if a cell is formatted as text and you enter a number, that number will be treated as text.)
Could you post a sample of your data if that doesn't help?

3. ## Re: nested IF formula (2000)

Thanks for your response, Rory. All of my range data was formatted as "general." I've tried changing everything to "text" with no success. Also tried "number" with no success. Tried a mixture with no success. I can put a sample database out there but it will take me a while to strip out company data, etc. Any other suggestions before I do that?

4. ## Re: nested IF formula (2000)

It works for me. Can you upload a workbook that shows the problem.

5. ## Re: nested IF formula (2000)

Here's the file. Cell G4 has the working formula. The cell references for the three conditions should be: \$A\$18, \$B19, and \$G\$11, respectively. Thanks!!

6. ## Re: nested IF formula (2000)

You can see that by using in G19:

You can either re-create your "Team" column which you format as text before entering anything, or you use the above formula or its equivalent which you don't need entering with control+shift+enter:

After repairing the "Team" column, you can remove the &"" bit.