I the following information:

In Row 14 are city names, In Column A, are item descriptions

I have data (numbers) in B15:AQ249.

I want to return the number in the range B15:AQ249 for a particular
city, however, when I use the SumProduct formula below, it returns the
total of the row, but I want just one number for a particular city.

How can I make this formula work?


=SUMPRODUCT((B14:AQ14="Bethesda")*0)+SUMPRODUCT((A 15:A80=A4)*(B15:AQ80))


Thanks

TR


--
bountifulgrace
------------------------------------------------------------------------
bountifulgrace's Profile: http://www.excelforum.com/member.php...o&userid=33237
View this thread: http://www.excelforum.com/showthread...hreadid=538942

=INDEX(B15:AQ80,MATCH(A4,A15:A80,0),MATCH("Bethesd a",B14:AQ14,0))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bountifulgrace"
<bountifulgrace.27aitz_1146758404.5249@excelforu m-nospam.com wrote in
message news:bountifulgrace.27aitz_1146758404.5249@excelfo rum-nospam.com...

I the following information:

In Row 14 are city names, In Column A, are item descriptions

I have data (numbers) in B15:AQ249.

I want to return the number in the range B15:AQ249 for a particular
city, however, when I use the SumProduct formula below, it returns the
total of the row, but I want just one number for a particular city.

How can I make this formula work?


=SUMPRODUCT((B14:AQ14="Bethesda")*0)+SUMPRODUCT((A 15:A80=A4)*(B15:AQ80))


Thanks

TR


--
bountifulgrace
------------------------------------------------------------------------
bountifulgrace's Profile:
http://www.excelforum.com/member.php...o&userid=33237
View this thread: http://www.excelforum.com/showthread...hreadid=538942

This works.

Thanks



Bob Phillips Wrote:
=INDEX(B15:AQ80,MATCH(A4,A15:A80,0),MATCH("Bethesd a",B14:AQ14,0))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"bountifulgrace"
<bountifulgrace.27aitz_1146758404.5249@excelforu m-nospam.com wrote in
message
news:bountifulgrace.27aitz_1146758404.5249@excelfo rum-nospam.com...

I the following information:

In Row 14 are city names, In Column A, are item descriptions

I have data (numbers) in B15:AQ249.

I want to return the number in the range B15:AQ249 for a particular
city, however, when I use the SumProduct formula below, it returns
the
total of the row, but I want just one number for a particular city.

How can I make this formula work?



=SUMPRODUCT((B14:AQ14="Bethesda")*0)+SUMPRODUCT((A 15:A80=A4)*(B15:AQ80))


Thanks

TR


--
bountifulgrace

------------------------------------------------------------------------
bountifulgrace's Profile:
http://www.excelforum.com/member.php...o&userid=33237
View this thread:
http://www.excelforum.com/showthread...hreadid=538942



--
bountifulgrace
------------------------------------------------------------------------
bountifulgrace's Profile: http://www.excelforum.com/member.php...o&userid=33237
View this thread: http://www.excelforum.com/showthread...hreadid=538942