Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumif with similar cells
I'm not sure how to do this.
A B 34-f-r 33 34-f-r 56 34-f-k 44 34-f-t 99 34-f-t 82 I want to add column B if A is the same, so I want to make anothe sheet with cells in column A no repeated but all the values in column B added. A B 34-f-r 89 34-f-k 44 34-f-t 181 Thanks and congratulations to all the helpers. Martin |
#2
|
|||
|
|||
A pivot Table (pulled down from Data) will generate the sum for each unique
entry in the first column. -- Gary's Student "Mosqui" wrote: I'm not sure how to do this. A B 34-f-r 33 34-f-r 56 34-f-k 44 34-f-t 99 34-f-t 82 I want to add column B if A is the same, so I want to make anothe sheet with cells in column A no repeated but all the values in column B added. A B 34-f-r 89 34-f-k 44 34-f-t 181 Thanks and congratulations to all the helpers. Martin |
#3
|
|||
|
|||
On Sheet2, enter this in A1 and copy down to A5:
=IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)=1,Sheet1!A1, "") This will give you a list of unique entries from Column A on Sheet1. This list will have blank rows, where the duplicate entries were eliminated. In B1 of Sheet2, enter this formula: =SUMIF(Sheet1!A1:A5,A1,Sheet1!B1:B5) And copy down to B5. This will give you the totals of the unique entries displayed in Column A. NOW, if you would like to eliminate the empty rows in Column A of Sheet2, you can check out this web page of Chip Pearson, where he has instructions on using a formula to do just that. http://www.cpearson.com/excel/noblanks.htm After you get rid of the blank rows, you can adjust the SumIf formula to reference the new, non-blank column. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Mosqui" wrote in message ... I'm not sure how to do this. A B 34-f-r 33 34-f-r 56 34-f-k 44 34-f-t 99 34-f-t 82 I want to add column B if A is the same, so I want to make anothe sheet with cells in column A no repeated but all the values in column B added. A B 34-f-r 89 34-f-k 44 34-f-t 181 Thanks and congratulations to all the helpers. Martin |
#4
|
|||
|
|||
Hi,
Assumed your data range start from Sheet1, A1 to B5 In your new sheet, try... =SUMIF(Sheet1!$A$1:$A$5,A1,Sheet1!$B$1:$B$5) "Mosqui" wrote in message ... I'm not sure how to do this. A B 34-f-r 33 34-f-r 56 34-f-k 44 34-f-t 99 34-f-t 82 I want to add column B if A is the same, so I want to make anothe sheet with cells in column A no repeated but all the values in column B added. A B 34-f-r 89 34-f-k 44 34-f-t 181 Thanks and congratulations to all the helpers. Martin |
#5
|
|||
|
|||
Thanks once again. Is working OK.
"RagDyer" wrote: On Sheet2, enter this in A1 and copy down to A5: =IF(COUNTIF(Sheet1!$A$1:A1,Sheet1!A1)=1,Sheet1!A1, "") This will give you a list of unique entries from Column A on Sheet1. This list will have blank rows, where the duplicate entries were eliminated. In B1 of Sheet2, enter this formula: =SUMIF(Sheet1!A1:A5,A1,Sheet1!B1:B5) And copy down to B5. This will give you the totals of the unique entries displayed in Column A. NOW, if you would like to eliminate the empty rows in Column A of Sheet2, you can check out this web page of Chip Pearson, where he has instructions on using a formula to do just that. http://www.cpearson.com/excel/noblanks.htm After you get rid of the blank rows, you can adjust the SumIf formula to reference the new, non-blank column. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Mosqui" wrote in message ... I'm not sure how to do this. A B 34-f-r 33 34-f-r 56 34-f-k 44 34-f-t 99 34-f-t 82 I want to add column B if A is the same, so I want to make anothe sheet with cells in column A no repeated but all the values in column B added. A B 34-f-r 89 34-f-k 44 34-f-t 181 Thanks and congratulations to all the helpers. Martin |
#6
|
|||
|
|||
Let A1:B6 on Sheet1 house the sample provided, including labels:
{"Item","Amount";"34-f-r",33;"34-f-r",56;"34-f-k",44;"34-f-t",99;"34-f-t",82} Thus: A1 is Item, B1 Amount, A2 34-f-r, etc. On Sheet1... C1: 0 which is mandatory. C2, copied down: =IF((A2<"")*ISNA(MATCH(A2,$A$1:A1,0)),LOOKUP(9.99 999999999999E+307,$C$1:C1)+1,"") On Sheet2... A1: =LOOKUP(9.99999999999999E+307,Sheet1!$C$1:$C$6) A2: Item A3, copied down: =IF(ROW()-ROW($A$3)+1<=$A$1,LOOKUP(ROW()-ROW($A$3)+1,Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6),"") which yields a no-blanks list of distinct items. B2: Total Amount B3, copied down: =IF(A3<"",SUMIF(Sheet1!$A$2:$A$6,A3,Sheet1!$B$2:$ B$6),"") which yields the desired subtotals per distinct item. Mosqui wrote: I'm not sure how to do this. A B 34-f-r 33 34-f-r 56 34-f-k 44 34-f-t 99 34-f-t 82 I want to add column B if A is the same, so I want to make anothe sheet with cells in column A no repeated but all the values in column B added. A B 34-f-r 89 34-f-k 44 34-f-t 181 Thanks and congratulations to all the helpers. Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
paste special | values should work with merged cells | Excel Discussion (Misc queries) | |||
adding summed cells in a conditional sumif | Excel Worksheet Functions | |||
REVISED ?? - populate limited cells w/ set value | Excel Worksheet Functions | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |