Results 1 to 7 of 7
Thread: nested IF formula (2000)

20020423, 17:53 #1
 Join Date
 Apr 2002
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20020423, 18:02 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,350
 Thanks
 4
 Thanked 228 Times in 210 Posts
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?Regards,
Rory
Microsoft MVP  Excel

20020423, 18:22 #3
 Join Date
 Apr 2002
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?
Thanks for your help.

20020423, 18:45 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: nested IF formula (2000)
It works for me. Can you upload a workbook that shows the problem.
Legare Coleman

20020423, 18:51 #5
 Join Date
 Apr 2002
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!!

20020423, 19:32 #6
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: nested IF formula (2000)
Your "Team" column has not the same underlying format as your "Testing ADU" column.
You can see that by using in G19:
{=SUM(IF((ADadu=A18&"")*(ADvic=B19)*(ADstatus=G11) ,1,0))}
You can either recreate 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:
=SUMPRODUCT((ADadu=A18&"")*(ADvic=B19)*(ADstatus=G 11))
After repairing the "Team" column, you can remove the &"" bit.
AladinMicrosoft MVP  Excel

20020424, 13:25 #7
 Join Date
 Apr 2002
 Posts
 7
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: nested IF formula (2000)
Thanks for your help.