| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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) |
|
#2
|
|||
|
|||
|
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) |
|
#3
|
|||
|
|||
|
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) . |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 . |
|
#6
|
|||
|
|||
|
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 . |
|
#7
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
| SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions | |||
| SUMIF HELP PLEASE | Excel Worksheet Functions | |||
| sumif to add data in multiple sheets | Excel Worksheet Functions | |||
| Modify SUMIF and COUNTIF to work with SUBTOTALS | Excel Worksheet Functions | |||
Linear Mode
