Whats the difference of these formulas?

  • Thread starter Thread starter A P
  • Start date Start date
A

A P

Hi guys!

I have very big problem. I have two formula to show and tell me the
difference:

Formula that doesn't work:
=SUM(IF(data!$F$2:$F$300="KP2B-PCB
Testing",IF(data!$B$2:$B$300="MLS54.2S",IF(data!$N$2:$N$300="Material
Related ",data!$D$2:$D$300,0),0),0))

Formula that is working:
=SUM(IF(data!$F$2:$F$300="KP2B-PCB
Testing",IF(data!$B$2:$B$300="MLS54.2S",IF(data!$N$2:$N$300="Material
Related ",data!$D$2:$D$300,0),0),0))

I can't take this anymore!!!

Me
 
Hi

Your formula must be an array formula, i.e. entered with Ctrl+Shift+Enter.
When you activate the cell fith formula, then in formula bar at top you must
see it in curly brackets, like this:
{=YourFormula}
When brackets are missing, then you probably edited the formula, and didn't
enter it as array formula afterwards - as result you get wrong answer.

You can use a non-array alternative:
=SUMPRODUCT(--(data!$F$2:$F$300="KP2B-PCB
Testing"),--(data!$B$2:$B$300="MLS54.2S"),--(data!$N$2:$N$300="Material
Related "),data!$D$2:$D$300)

Another thought - in 3rd condition you have a trailing space in both your
formulas. Is this space on right place there, and exist it in both your
formulas? Another possibility for error there.
 
Reply-To: "A P" <[email protected]>
From: "A P" <[email protected]>
Subject: Whats the difference of these formulas?
Date: Tue, 1 Mar 2005 14:50:09 +0800
Lines: 21
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <[email protected]>
Newsgroups: microsoft.public.excel
NNTP-Posting-Host: 202.163.232.10
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.excel:41133


Hi guys!

I have very big problem. I have two formula to show and tell me the
difference:

Formula that doesn't work:
=SUM(IF(data!$F$2:$F$300="KP2B-PCB
Testing",IF(data!$B$2:$B$300="MLS54.2S",IF(data!$N$2:$N$300="Material
Related ",data!$D$2:$D$300,0),0),0))

Formula that is working:
=SUM(IF(data!$F$2:$F$300="KP2B-PCB
Testing",IF(data!$B$2:$B$300="MLS54.2S",IF(data!$N$2:$N$300="Material
Related ",data!$D$2:$D$300,0),0),0))

I can't take this anymore!!!

Me
Click to expand...

LOL....Sorry, but they're identical for me, and valid, although the
result is highly dependent on the row that the formula is in. But, you
never said precisely what the "bad" output was. An error? An unexpected
result?
 
Or I may revise the formula, can you help me?

What I need is this:

I want to sum up values on D cells if:
F cells is equal to "KP3-Burn..." and
B cells is equal to "MLS..." and
N cells is equal to "Material..."

Can you give me a simple formula?
 
Just change the KP2B-PCB Testing value in Arvi's formula to the K3 Burn ...
value, and any others required.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top