Ooops!

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

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

Should be:

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

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Doc Behr" wrote in message
...
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:

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

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Doc Behr" <Doc wrote in message
...
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