Problem with sumif criteria

  • Thread starter Thread starter JulieIde
  • Start date Start date
J

JulieIde

Hi all,
Hope someone can help me.
I'm trying to sum a column value if another corresponding column value
is "4A".
For some reason this will work if the value is "FOO" but not "4A". I
think the number/alpha combo makes the spreadsheet think I'm trying to
refer to a cell location. I'm not. I want to match on the string
"4A".

Here is a formula that works:
=SUMIF(H286;"=FOO";K286)
Here is the one that does not:
=SUMIF(H286;"=4A";K286)

Does anyone know the syntax to get this to work? Any ideas?
Thanks!
Julie Ide
 
Julie,

I just tried it and it works the same for 4A as for FOO on my system (XP,
XL2000).

Are you sure you have 4A in the cell, not leading or trailing spaces? Test
by adding =LEN(H286) in some cell and see if it contains 2.

You also dont need to test for =4A, you can just test for 4A, the = is
assumed. You need to include other conditions (<> for example) though.

By the way, SUMIF is intended for arrays. I don't know if you intende to use
arrays but just posted a single cell as an example, but if not, you can
also use

=IF(H286="FOO",K286,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Julie!

It's working OK for me:

=SUMIF(H286,"=FOO",K286)
Returns the value of K286 if H286 is FOO

=SUMIF(H286,"=4A",K286)
Returns the value of K286 if H286 is 4A

I have had to change the argument separators to , but I assume you are
correctly using your separator of ;

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top