I have a worksheet with 2 tabs, Sheet 1 is to be used to display the data I
have on Sheet 2. I named Sheet 2 data and have the following columns:

Dealer Quantity on Invoice Description
Dealer A 1 Product A
Dealer B 1 Product B
Dealer A 1 Product A
Dealer B 1 Product A

On Sheet 1 I created a list in cell A1 so I could select my Dealer. On
Sheet 1 I have a list of the products starting in cell A21. I want to add
quantity sold based on cell A1 that has the dealer's name (thereby compiling
all the dealer's branches into 1) and by product determine what we've sold
them.

So, if A1=data!A:A and A21=data!C:C then sum all that matches in column
data!B:B and return that amount. I've tried sumif with and statements but I
just can't get it right. The closest I've come is
=SUM(IF(AND(($A$1=data!A:A),(Sheet1!A21=data!C:C)) ,data!B:B)) comes back at
least with a 0 and not an error message, what am I doing wrong.

Thanks

=SUM(IF(AND(($A$1=data!A:A),(Sheet1!A21=data!C:C)) ,data!B:B))

You can not use a whole column unless you have XL-2007, use "*" instead of
"AND"

Try it like this:

=SUM(IF(($A$1=data!A1:A100)*(Sheet1!A21=data!C1:C1 00),data!B1:B100))

Ctrl+shift+Enter, not just Enter
Adjust your range to suit


"doubleD" wrote:

I have a worksheet with 2 tabs, Sheet 1 is to be used to display the data I
have on Sheet 2. I named Sheet 2 data and have the following columns:

Dealer Quantity on Invoice Description
Dealer A 1 Product A
Dealer B 1 Product B
Dealer A 1 Product A
Dealer B 1 Product A

On Sheet 1 I created a list in cell A1 so I could select my Dealer. On
Sheet 1 I have a list of the products starting in cell A21. I want to add
quantity sold based on cell A1 that has the dealer's name (thereby compiling
all the dealer's branches into 1) and by product determine what we've sold
them.

So, if A1=data!A:A and A21=data!C:C then sum all that matches in column
data!B:B and return that amount. I've tried sumif with and statements but I
just can't get it right. The closest I've come is
=SUM(IF(AND(($A$1=data!A:A),(Sheet1!A21=data!C:C)) ,data!B:B)) comes back at
least with a 0 and not an error message, what am I doing wrong.

Thanks

I copied the formula into my spreadsheet and selected the actual column range
and it still came back with 0's as the result when I know the dealer
purchased items.

=SUM(IF(($A$1=data!A2:A1250)*(Sheet1!A21=data!C2:C 1250),data!B2:B1250))

"Teethless mama" wrote:

=SUM(IF(AND(($A$1=data!A:A),(Sheet1!A21=data!C:C)) ,data!B:B))

You can not use a whole column unless you have XL-2007, use "*" instead of
"AND"

Try it like this:

=SUM(IF(($A$1=data!A1:A100)*(Sheet1!A21=data!C1:C1 00),data!B1:B100))

Ctrl+shift+Enter, not just Enter
Adjust your range to suit


"doubleD" wrote:

I have a worksheet with 2 tabs, Sheet 1 is to be used to display the data I
have on Sheet 2. I named Sheet 2 data and have the following columns:

Dealer Quantity on Invoice Description
Dealer A 1 Product A
Dealer B 1 Product B
Dealer A 1 Product A
Dealer B 1 Product A

On Sheet 1 I created a list in cell A1 so I could select my Dealer. On
Sheet 1 I have a list of the products starting in cell A21. I want to add
quantity sold based on cell A1 that has the dealer's name (thereby compiling
all the dealer's branches into 1) and by product determine what we've sold
them.

So, if A1=data!A:A and A21=data!C:C then sum all that matches in column
data!B:B and return that amount. I've tried sumif with and statements but I
just can't get it right. The closest I've come is
=SUM(IF(AND(($A$1=data!A:A),(Sheet1!A21=data!C:C)) ,data!B:B)) comes back at
least with a 0 and not an error message, what am I doing wrong.

Thanks

Don't forget to press CTRL+SHIFT+ENTER, not just ENTER. When you do it right
it will put the curly brackets around the formula.


"doubleD" wrote:

I copied the formula into my spreadsheet and selected the actual column range
and it still came back with 0's as the result when I know the dealer
purchased items.

=SUM(IF(($A$1=data!A2:A1250)*(Sheet1!A21=data!C2:C 1250),data!B2:B1250))

"Teethless mama" wrote:

=SUM(IF(AND(($A$1=data!A:A),(Sheet1!A21=data!C:C)) ,data!B:B))

You can not use a whole column unless you have XL-2007, use "*" instead of
"AND"

Try it like this:

=SUM(IF(($A$1=data!A1:A100)*(Sheet1!A21=data!C1:C1 00),data!B1:B100))

Ctrl+shift+Enter, not just Enter
Adjust your range to suit


"doubleD" wrote:

I have a worksheet with 2 tabs, Sheet 1 is to be used to display the data I
have on Sheet 2. I named Sheet 2 data and have the following columns:

Dealer Quantity on Invoice Description
Dealer A 1 Product A
Dealer B 1 Product B
Dealer A 1 Product A
Dealer B 1 Product A

On Sheet 1 I created a list in cell A1 so I could select my Dealer. On
Sheet 1 I have a list of the products starting in cell A21. I want to add
quantity sold based on cell A1 that has the dealer's name (thereby compiling
all the dealer's branches into 1) and by product determine what we've sold
them.

So, if A1=data!A:A and A21=data!C:C then sum all that matches in column
data!B:B and return that amount. I've tried sumif with and statements but I
just can't get it right. The closest I've come is
=SUM(IF(AND(($A$1=data!A:A),(Sheet1!A21=data!C:C)) ,data!B:B)) comes back at
least with a 0 and not an error message, what am I doing wrong.

Thanks