If column contains value, return the sum of another column

[alloakland1]

I would like to return the sum of a column if another column equals a criteria.

For example:
This is a data sheet that I am getting my info from (Sheet1)
A1 company B Location C Total Gross
2.Vizio San Francisco $100
3.Sony Denver $1000
4.Samsung New York $200
5.Apple Denver $400

I would like to put a formula in another sheet (Sheet2) with the list of companies in Column A and Location in the first row.
I've tried =IF(Sheet1!A:A="Vizio",SUMIF(Sheet1!B:B,"San Francisco",Sheet1!C:C),0)

How would I return Total Gross based on both the company and location?

 

[Jonmo1]

Which version of Excel are you using?

In XL2007+ we have sumifS
=SUMIFS(Sheet1!C:C,Sheet1!A:A,"Vizio",Sheet1!B:B,"San Francisco")

XL2003 or earlier, you need sumproduct
=SUMPRODUCT(--(Sheet1!A1:A1000="Vizio"),--(Sheet1!B1:B1000="San Francisco"),Sheet1!C1:C1000)

Note, you can't use entire column references in sumproduct xl2003, must specify row #s

 

[alloakland1]

I unfortunately have excel 2003

 

[alloakland1]

WOw you are a life saver THANK YOU!!!!

 

[Jonmo1]

ADVERTISEMENT

Glad to help, thanks for the feedback.

 

[alloakland1]

One last question... how do I use the formula if i wanted to count if Total Gross doesn't equal 0?

I tried this formula but didn't work: =SUMPRODUCT(--(Sheet1!A1:A1000="Vizio"),--(Sheet1!B1:B1000="San Francisco"),Sheet1!C1:C1000<>0)

 

[Jonmo1]

ADVERTISEMENT

Almost, try

I tried this formula but didn't work: =SUMPRODUCT(--(Sheet1!A1:A1000="Vizio"),--(Sheet1!B1:B1000="San Francisco"),--(Sheet1!C1:C1000<>0))

 

[alloakland1]

THANKS AGAIN MR MVPPPPPPPPPPP!!!!!! You ARE THe MVP!

 

[Jonmo1]

You're welcome.