SUMPRODUCT

[Guest]

Hi everyone

Can anyone help me?

A while back Biff supplied me with a formula that found a city name in two
different counties and added them together.

=sumproduct(--(index(ilsort,,1)="chicago"),--(index(ilsort,,2)="cook")+(index(ilsort,,2) = "page"),index(ilsort,,4))

This worked great. However, now I need to add several different cities in
the same county. example darrow + geismar+prairieville in Ascension
county.

There can be the same city in another county but I need only the cities in
this county to be added together

I have moved things around from the above formula but it only seems to read
the 1st part and return the data. It doesn't add. I just can't get it. I'm
not sure what argument needs to come first.

The formula below is use when nothing needs to get added.

IF(ISNA(INDEX(ilsort,MATCH(1,(INDEX(ilsort,,1)="Totals for City:
SCHAUMBURG")*(INDEX(ilsort,,2)=" County:
COOK"),0),4)),0,INDEX(ilsort,MATCH(1,(INDEX(ilsort,,1)="Totals for City:
SCHAUMBURG")*(INDEX(ilsort,,2)=" County: COOK"),0),4))

Thanks so much.

 

[Bob Phillips]

How about

=SUMPRODUCT((index(ilsort,,1)="chicago","Darrow")*(index(ilsort,,2)={"cook",
"page","Asension"})*(index(ilsort,,4))

as an example


--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi everyone

Can anyone help me?

A while back Biff supplied me with a formula that found a city name in two
different counties and added them together.

=sumproduct(--(index(ilsort,,1)="chicago"),--(index(ilsort,,2)="cook")+(inde
x(ilsort,,2) = "page"),index(ilsort,,4))

 

[Guest]

thanks you! I can't seem to get it to work, but I'll keep working on it. I
must be missing a comma or something. I only listed one county because all
of the cities are in the ascenion county. Will this make a difference?


thanks again.

 

[Bob Phillips]

No, but omit the {} here, and I missed a pair of {}, like so

=SUMPRODUCT((index(ilsort,,1)={"darrow","geismar","prairieville"})*(index(il
sort,,2)="Asension")*(index(ilsort,,4))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, it worked in my test perfectly.

I did a test on the same page, I use part of the original data and it
worked great.

I copied the formula to my original and change the test range to the actual
range name and I received "#value". Do you know why it would do this. The
test proved that the names I had typed in were exact to the data. In the
original the the data is on another worksheet. would the location of the
data make a difference?

thanks,

 

[Bob Phillips]

Shouldn't do as you use a name. Make sure that the name ilsort is correctly
defined.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi,

I use the F3 key to pull the range name. I'm sure it will work out. I'll
just keep looking.

Thanks so very much for your help.