|
SumProduct Formula Help
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 |
SumProduct Formula Help
=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 |
SumProduct Formula Help
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 |
| All times are GMT +1. The time now is 11:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com