Results 1 to 7 of 7
  1. #1
    New Lounger
    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?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  3. #3
    New Lounger
    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.

  4. #4
    Uranium Lounger
    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

  5. #5
    New Lounger
    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!!
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    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 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:

    =SUMPRODUCT((ADadu=A18&"")*(ADvic=B19)*(ADstatus=G 11))

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

    Aladin
    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: nested IF formula (2000)

    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •