conditional sum formula
 

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
------------------------------------------------------------------------
erwt's Profile: http://www.excelforum.com/member.php...o&userid=26993
View this thread: http://www.excelforum.com/showthread...hreadid=401994

i've got it!!! SUM.IF


--
erwt
------------------------------------------------------------------------
erwt's Profile: http://www.excelforum.com/member.php...o&userid=26993
View this thread: http://www.excelforum.com/showthread...hreadid=401994

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


--
roly
------------------------------------------------------------------------
roly's Profile: http://www.excelforum.com/member.php...o&userid=24631
View this thread: http://www.excelforum.com/showthread...hreadid=401994

A much better way is to use SUMPRODUCT.

Assuming your column "ID" starts in A1 (values start in A2) and your
"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 this
could be any cell of your choice). For those rows that match, the
corresponding values in B2:B7 are summed. If you have "4" entered in
D1, the formula will result in 22.


Coober


--
Coober
------------------------------------------------------------------------
Coober's Profile: http://www.excelforum.com/member.php...o&userid=27004
View this thread: http://www.excelforum.com/showthread...hreadid=401994