adding up sums only if condition is met

[Doc Behr]

Here's an example of a spread sheet:

A B
1 1 $55.25
2 $997.00
3 $693.36
4 1 $535.00
5 1 $325.12
6 $636.36
7 1 $1009.00


can someone help me with an function to help me add up the total of coloumn
B only if coloumn A has a "1"

I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7)
and it wasn't working, i think this is close though

 

[T. Valko]

I tried this: =SUMPRODUCT(--(Sheet1!A1:A7="1"),Sheet1!B1:B7)

and it wasn't working, i think this is close though

Pretty close! Just remove the quotes from around 1:

=SUMPRODUCT(--(Sheet1!A1:A7=1),Sheet1!B1:B7)

Here's a couple of alternatives...

If column A contains only the number 1 or blank/empty cells:

=SUMPRODUCT(Sheet1!A1:A7,Sheet1!B1:B7)

This is the best option:

=SUMIF(Sheet1!A1:A7,1,Sheet1!B1:B7)

 

[Doc Behr]

Awsome, thanks for all the helpful hints, now i have one more question
related to this.

how do i search for 2 variables in two coloumns and then add up the sum of
the 3rd coloumn. For example:

how would i add on to this formula to only add up coloumn B if in coloumn A
there is a "1" and in coloumn C there is a "AJ"?

 

[T. Valko]

Try this:

=SUMPRODUCT(--(Sheet1!A1:A7=1),--(Sheet1!A1:A7="AJ"),Sheet1!B1:B7)

Better to use cells to hold the criteria:

A1 = 1
B1 = AJ

=SUMPRODUCT(--(Sheet1!A1:A7=A1),--(Sheet1!A1:A7=B1),Sheet1!B1:B7)

If you're using Excel 2007:

=SUMIFS(Sheet1!B1:B7,Sheet1!A1:A7,A1,Sheet1!C1:C7,B1)

 

[Ragdyer]

No disrespect intended, BUT, after Biff showed you the correction to *your*
original Sumproduct formula, you can't figure this out for yourself?

 

[T. Valko]

Ooops!

"typo"....that's what I get for copying/pasting!

=SUMPRODUCT(--(Sheet1!A1:A7=1),--(Sheet1!A1:A7="AJ"),Sheet1!B1:B7)

Should be:

 

[JP Ronse]

Hi Doc,

I recommend reading Chip Pearsons pages about formulas.
http://www.cpearson.com/excel/MainPage.aspx

(search formula) and you will find lot of examples on how to use conditions
in formulas.

Wkr,

JP