nested IF formula (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!!

Reply | Quote

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=G11))

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

Aladin

Reply | Quote

Thanks for your help.

Reply | Quote