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

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

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

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

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

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