# conditional sum formula

1. ## 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  Register To Reply

2. i've got it!!! SUM.IF  Register To Reply

3. 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  Register To Reply

4. 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  Register To Reply