conditional sum formula

[erwt]

I need a conditional sum formula. this is my sheet:

A - B
id - price
1 - 10
2 - 10
3 - 15
4 - 10
4 - 7
4 - 5

I want to calculate the totalsum of id 4. So i want to get the value
22. I searching now for 2 hours and i cant find a possible formula.
Who can help me!

Thnx

 

[erwt]

i've got it!!! SUM.IF

 

[roly]

Hi,

A conditional sum function is SUMIF. It works like this:
=SUMIF(Range,Criteria,Sum Range). The range is the cells you wish to
look for the criteria in; the criteria is what you are looking for,;
the sum range is the cells you want to add up.

The formula you would use for your data below is:

SUMIF(A2:A7,"4",B2:B7)

You can also refer to cells in the criteria, which is useful if you
have a large amount of data.

All the best,


Roly

 

[Coober]

A much better way is to use SUMPRODUCT.

Assuming your column "ID" starts in A1 (values start in A2) and you
"PRICES" values start in B2.

Enter:

=SUMPRODUCT((A2:A7=D1)*(B2:B7))

This looks in the range A2:A7 for values that match D1 (of course thi
could be any cell of your choice). For those rows that match, th
corresponding values in B2:B7 are summed. If you have "4" entered i
D1, the formula will result in 22.


Coobe