Sum a range meeting criteria

[billbran]

I am trying to figure out how to sum costs associated with a specific
account as follows:

A B C

account cost

1 1000 $600

2 1100 $500

3 1100 $700

4 1200 $200

5 1200 $50

6 700 $100

I want to show cell c1 as the sum of all $ amounts for account 1000,
and show cell c3 as the sum of all $ amounts for account 1100, show
cell c5 as the sum of all $ amounts for account 1200 etc. I do not
want anything to appear in column C unless it is the total of the
account sum, and that should appear in the last row of each account.

Any suggestions are GREATLY appreciated! Thanks

 

[Biff]

Hi!

C1 =SUMIF(A1:A7,1000,B1:B7)

C3 =SUMIF(A1:A7,1100,B1:B7)

C5 =SUMIF(A1:A7,1200,B1:B7)

etc
etc

I do not want anything to appear in column C unless it is
the total of the account sum, and that should appear in
the last row of each account.

I don't understand what that means!

Biff

 

[Arvi Laanemets]

Hi

C1=IF(AND(A1<>"",COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1)),SUMIF(A:A,A1,B:B),"")
and copy it down

Arvi Laanemets

 

[Aladin Akyurek]

Let A1:B7 house the sample you provided, with the labels "account" and
"cost" in A1:B1.

In C2 enter & copy down:

=IF(ISNUMBER(MATCH(A2,A3:$A$8,0)),"",SUMIF($A$2:A2,A2,$B$2:B2))

Note that the MATCH refers to a range with A8 as the end cell. That is, one
cell more than the actual range. The formula can pick up the last account #
if only if there is an empty row immediately after the last data row.

If you sort the data on the account column, you can switch to:

=IF(A2<>A3,SUMIF($A$2:A2,A2,$B$2:B2),"")