Sumif with multple criteria

[clane]

Hello all,

I have a very large sheet that has a list of locations and thei
coorisponding location code and I have a list of new locations tha
need this location code.

I have been trying to use sumif to get this location code on the ne
sheet. My problem is that I have to search by city but some cities hav
the same names in different states so what i need to do is

inside the sheet
if state = and city = then return the city code

i can do it by zip code and it works where you just say

=sumif(zipcode list, zipcode, city code)/countif(zipcode list
zipcode)

but i need to get to the areas of the list with no zipcodes

also many of these should exist more than once in the list but all
need to do is find 1 occurnece to be sure its the rigth city code.

any help would be apreciated


thanks

Chuc

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--(A1:A100="state"),--(B1:B100="city"),C1:C100)

For more see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[clane]

Thanks for the resonse frank,

I think this is on the right track but i want to return the city cod
so heres what i have so far

sumproduct(countif(states list , state), countif(city list, city),

if i just put the 3rd array as c1:c66000 where this colum contains th
city code values will this formula return my city code for where th
state and city match?

thanks million

chuc

 

[clane]

also what does "--" signify?

thank

 

[Frank Kabel]

Hi
then you need something else. Try the following array formula (entered
with CTRL+SHIFT+eNTER):
=INDEX(C1:C10000,MATCH(1,(A1:A10000="state")*(B1:B10000="City"),0))

 

[Frank Kabel]

It coerces the boolean values (TRUE/FALSE) to real numbers (1/0)

 

[clane]

Frank,

You rule thanks so much you just saved me many hours of work!

thanks again

Chuc