|
SUMIF not calculating
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$245 10) |
Hi
what is in cell BE8128 and give some example data for your range AR... -- Regards Frank Kabel Frankfurt, Germany "KENNY" schrieb im Newsbeitrag ... 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$245 10) |
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 -----Original Message----- Hi what is in cell BE8128 and give some example data for your range AR... -- Regards Frank Kabel Frankfurt, Germany "KENNY" schrieb im Newsbeitrag ... 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$245 10) . |
KENNY wrote... 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 ... "KENNY" schrieb ... =SUMIF($AP$8128:$AP$24510,$BE8128,$AR$8128:$AR$ 24510) Try =SUMIF($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128 :$AR$24510) -- hgrove ------------------------------------------------------------------------ hgrove's Profile: http://www.excelforum.com/member.php...o&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=319403 |
Thanks for the reply, but no luck
-----Original Message----- KENNY wrote... 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 ... "KENNY" schrieb ... =SUMIF($AP$8128:$AP$24510,$BE8128,$AR$8128:$AR $24510) Try =SUMIF ($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128:$AR$2 4510) -- hgrove ---------------------------------------------------------- -------------- hgrove's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=319403 . |
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 -- Regards Frank Kabel Frankfurt, Germany "KENNY" schrieb im Newsbeitrag ... Thanks for the reply, but no luck -----Original Message----- KENNY wrote... 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 ... "KENNY" schrieb ... =SUMIF($AP$8128:$AP$24510,$BE8128,$AR$8128:$AR $24510) Try =SUMIF ($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128:$AR$2 4510) -- hgrove ---------------------------------------------------------- -------------- hgrove's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=319403 . |
Thanks Frank
Below are the three different approaches I tried: =SUMIF ($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128:$AR$2 4510) =SUMPRODUCT((AP8128:AP24510=BE8128)*(AR8128:AR2451 0)) =SUMIF($AP$8128:$AP$24510,$BE8128,$AR$8128:$AR$245 10) They all return: #VALUE! -----Original Message----- 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 -- Regards Frank Kabel Frankfurt, Germany "KENNY" schrieb im Newsbeitrag ... Thanks for the reply, but no luck -----Original Message----- KENNY wrote... 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 ... "KENNY" schrieb ... =SUMIF ($AP$8128:$AP$24510,$BE8128,$AR$8128:$AR$24510) Try =SUMIF ($AP$8128:$AP$24510,"*"&$BE8128&"*",$AR$8128:$AR$2 4510) -- hgrove ------------------------------------------------------- --- -------------- hgrove's Profile: http://www.excelforum.com/member.php? action=getinfo&userid=11432 View this thread: http://www.excelforum.com/showthread...hreadid=319403 . . |
| All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com