| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sum using text and numbers as criteria
I hope someone can assist with this......
I have a spreadsheet with column A containing words, and column b containing numbers A B C D (TOTAL) JOHN $123.00 GARY GARY $456.00 PAUL PAUL $789.00 JOHN JOHN $345.00 GARY $567.00 PAUL $678.00 I need a formula for column D to add totals for each name. I have tried SUMIF(A2:A7,"JOHN",B2:B7) but it doesn't add the amounts, only the first instance of JOHN is displayed by the formula. I have tried various other formulas found here in this discussion group but the SUMIF above is about the closest I've come to any joy, all the others display an error message. any suggestions would be greatly appreciated, this is really doing my head in! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum using text and numbers as criteria
Maybe you have hidden characters, assume the distinct name list is in C2:C4,
in D2 put =SUMIF($A$2:$A$7,C2,$B$2:$B$7) copy down to D4 now if that only give you 123 for John try this =SUMIF($A$2:$A$7,"*"&C2&"*",$B$2:$B$7) and copy down -- Regards, Peo Sjoblom "sevi61" wrote in message ... I hope someone can assist with this...... I have a spreadsheet with column A containing words, and column b containing numbers A B C D (TOTAL) JOHN $123.00 GARY GARY $456.00 PAUL PAUL $789.00 JOHN JOHN $345.00 GARY $567.00 PAUL $678.00 I need a formula for column D to add totals for each name. I have tried SUMIF(A2:A7,"JOHN",B2:B7) but it doesn't add the amounts, only the first instance of JOHN is displayed by the formula. I have tried various other formulas found here in this discussion group but the SUMIF above is about the closest I've come to any joy, all the others display an error message. any suggestions would be greatly appreciated, this is really doing my head in! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum using text and numbers as criteria
only the first instance of JOHN is displayed by the formula
What exactly does that mean? Do you mean that this formula: SUMIF(A2:A7,"JOHN",B2:B7) Returns only 123 when it should return 468? If you have the unique names listed in the range of cells C2:C4 then you need to make some of your references absolute: =SUMIF(A$2:A$7,C2,B$2:B$7) The $ signs will keep the row references from changing when you copy the formula. -- Biff Microsoft Excel MVP "sevi61" wrote in message ... I hope someone can assist with this...... I have a spreadsheet with column A containing words, and column b containing numbers A B C D (TOTAL) JOHN $123.00 GARY GARY $456.00 PAUL PAUL $789.00 JOHN JOHN $345.00 GARY $567.00 PAUL $678.00 I need a formula for column D to add totals for each name. I have tried SUMIF(A2:A7,"JOHN",B2:B7) but it doesn't add the amounts, only the first instance of JOHN is displayed by the formula. I have tried various other formulas found here in this discussion group but the SUMIF above is about the closest I've come to any joy, all the others display an error message. any suggestions would be greatly appreciated, this is really doing my head in! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum using text and numbers as criteria
thanks for all your help, this has helped me no end
"sevi61" wrote: I hope someone can assist with this...... I have a spreadsheet with column A containing words, and column b containing numbers A B C D (TOTAL) JOHN $123.00 GARY GARY $456.00 PAUL PAUL $789.00 JOHN JOHN $345.00 GARY $567.00 PAUL $678.00 I need a formula for column D to add totals for each name. I have tried SUMIF(A2:A7,"JOHN",B2:B7) but it doesn't add the amounts, only the first instance of JOHN is displayed by the formula. I have tried various other formulas found here in this discussion group but the SUMIF above is about the closest I've come to any joy, all the others display an error message. any suggestions would be greatly appreciated, this is really doing my head in! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
sum using text and numbers as criteria
You're welcome. Thanks for thr feedback!
-- Biff Microsoft Excel MVP "sevi61" wrote in message ... thanks for all your help, this has helped me no end "sevi61" wrote: I hope someone can assist with this...... I have a spreadsheet with column A containing words, and column b containing numbers A B C D (TOTAL) JOHN $123.00 GARY GARY $456.00 PAUL PAUL $789.00 JOHN JOHN $345.00 GARY $567.00 PAUL $678.00 I need a formula for column D to add totals for each name. I have tried SUMIF(A2:A7,"JOHN",B2:B7) but it doesn't add the amounts, only the first instance of JOHN is displayed by the formula. I have tried various other formulas found here in this discussion group but the SUMIF above is about the closest I've come to any joy, all the others display an error message. any suggestions would be greatly appreciated, this is really doing my head in! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to add numbers using criteria from another column | Excel Discussion (Misc queries) | |||
| Sum numbers based on specified criteria | Excel Worksheet Functions | |||
| sum numbers under or over a criteria? | Excel Worksheet Functions | |||
| add numbers if they meet criteria...? | Excel Worksheet Functions | |||
| Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
Linear Mode
