Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 29
Default Getting #Value error on Sumproduct with non-contiguous cells

I'm trying sumproduct, where one of the arrays is non-contiguous:

SUMPRODUCT(($AB$607:$AB$615=TRUE)*(AY583,BD583,BE5 83,BF583,BG583,BH583,BK583,BL583,BM583))

The result in receiving a #Value error. Any suggestions or workarounds.

Thanks...AJ
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Getting #Value error on Sumproduct with non-contiguous cells

AJ,

SUMPRODUCT appears to require you to have matching array sizes AND they must be contiguous. If you only have those 9 pairs to calculate you can do something like =AB607*AY583+AB608*BD583+...AB615*BM583

Your other option is to recreate the data elsewhere in the sheet by linking the cells. So if the new location is going to be at the beginning of a sheet make A1 =AB607, B1 =AY583, etc. and then you can use SUMPRODUCT(A1:A9,B1:B9). A variant on that would be to construct the array on a separate worksheet and pass the value back to the location you want on the original worksheet.

Hope that helps you
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Non-contiguous range and Sumproduct to average Mifty Excel Worksheet Functions 5 April 13th 10 03:03 AM
Error Message - Input Range must be a contiguous Reference Help!!! StuckBim!! Excel Discussion (Misc queries) 0 January 1st 10 11:06 AM
Copy and Paste LAST ROW of data: non-contiguous Row, contiguous Column Sam via OfficeKB.com Excel Programming 8 November 5th 07 08:18 PM
Error 1004 using not contiguous data Liughi Charts and Charting in Excel 1 January 15th 06 09:38 PM
how can i use SUMPRODUCT with a non-contiguous set of cells? Wade Wilson Excel Discussion (Misc queries) 1 May 9th 05 07:24 PM


All times are GMT +1. The time now is 02:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"