SUMIF not calculating

[KENNY]

I have the SUMIF formula below that works if I manually
enter in the underlying data I wish to sum. I have tried
to clean up the data with TRIM and Value. When I do a
simple sum on this cleaned data, it works. When I use
this function is doesn't! I don't get it..

Help?!


=SUMIF($AP$8128:$AP$24510,$BE8128,$AR$8128:$AR$24510)

 

[Frank Kabel]

Hi
what is in cell BE8128 and give some example data for your range AR...

 

[KENNY]

Hi Frank,

cell BE8128 is a word (I've substituted the actual word
instead of the cell reference, to no success.

Column AP Column AR
Dog 100
Cat 50
Dog 120
(Blank) (blank)
Cat 66

 

[hgrove]

KENNY wrote...

cell BE8128 is a word (I've substituted the actual word instead of th cell
reference, to no success.

Column AP Column AR
Dog 100
Cat 50
Dog 120
(Blank) (blank)
Cat 66 ...

Try

=SUMIF($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128:$AR$24510

 

[KENNY]

Thanks for the reply, but no luck

-----Original Message-----

KENNY wrote... instead of the
cell
($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128:$AR$24510)
--------------
hgrove's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11432
View this thread: http://www.excelforum.com/showthread.php?threadid=319403

.

 

[Frank Kabel]

Hi
Harlan's formula should work even if you have leading/trailing
characters in your source data. Could you post the EXACT formula you
have tried and what the returned result was

 

[KENNY]

Thanks Frank

Below are the three different approaches I tried:


=SUMIF
($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128:$AR$24510)

=SUMPRODUCT((AP8128:AP24510=BE8128)*(AR8128:AR24510))

=SUMIF($AP$8128:$AP$24510,$BE8128,$AR$8128:$AR$24510)


They all return: #VALUE!