Sum if two conditions met

D

doubleD

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
 
T

Teethless mama

=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:C100),data!B1:B100))

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

doubleD

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:C1250),data!B2:B1250))
 
T

Teethless mama

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top