+ Reply to Thread
Results 1 to 13 of 13

SUMPRODUCT where the range is generated by a function?

  1. #1
    Registered User
    Join Date
    01-14-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    21

    SUMPRODUCT where the range is generated by a function?

    Does the fact that the range is generated by a function affect the SUMPRODUCT function?

    In this particular spreadsheet I have used the following formula without issue:

    =SUMPRODUCT(--(MONTH(Quotes!$K$2:$K3139)=8),--(YEAR(Quotes!$K$2:$K3139)=2013),--(Quotes!$F$2:$F3139))

    where column F contains manually input numbers

    However, when I try to use this formula:

    =SUMPRODUCT(--(MONTH(Quotes!$K$2:$K3139)=8),--(YEAR(Quotes!$K$2:$K3139)=2013),--(Quotes!$T$2:$T3139))

    I get a #VALUE error.

    The primary difference is that the numbers in column T are generated by a formula working on other cells in the worksheet. Column T does also contain blank cells (also generated by the formula) but I have never had a problem with blank cells in my data range before so long as I use the -- syntax.

    Can anyone offer any advice?

  2. #2
    Registered User
    Join Date
    01-28-2014
    Location
    Brno
    MS-Off Ver
    Excel 2010
    Posts
    70

    Re: SUMPRODUCT where the range is generated by a function?

    Can you attach a sample of your workbook? Might be excel doesn't recognize inputs as numbers but it's hard to judge without real data..

  3. #3
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: SUMPRODUCT where the range is generated by a function?

    Please attach your sample workbook
    Cheers!
    Deep Dave

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,000

    Re: SUMPRODUCT where the range is generated by a function?

    Suggests invalid data e.g text or non-numeric values. Try filter on column T to see if highlights any "odd" values

  5. #5
    Registered User
    Join Date
    01-14-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: SUMPRODUCT where the range is generated by a function?

    Apart from the fact the numbers are generated by formula - this formula to be exact =IF(ISBLANK(S1128),"",((YEAR(S1128)-YEAR(K1128))*12+MONTH(S1128)-MONTH(K1128))) - there is nothing odd in column T. Just numbers, blanks and zeroes. Some of the numbers are negative but this has never caused a problem before.

    I attach a sample workbook. For confidentiality reasons I have had to remove the data from all the columns except the three involved in this formula, but for clarity's sake I have left all the data in these columns.
    Attached Files Attached Files

  6. #6
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: SUMPRODUCT where the range is generated by a function?

    The sample provided does not show what is there in the T Column as well as the F column..

    Which is the problem point here right?

  7. #7
    Registered User
    Join Date
    01-14-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: SUMPRODUCT where the range is generated by a function?

    The F column was just an example of the fact that this SUMPRODUCT formula works perfectly fine when the range is a list of manually entered numbers.
    If it helps, I can add a list of manually entered numbers, but the issue is that a formula I expected to work is giving a value error and the only reason I can see is that the numbers in the range (column T) are generated by a formula.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT where the range is generated by a function?

    You'll get the #VALUE! error if there are TEXT entries in the sum range.

    See if this version makes a difference:

    =SUMPRODUCT(--(TEXT(Quotes!$K$2:$K3139,"mmmyyyy")="Aug2013"),Quotes!$T$2:$T3139)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  9. #9
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: SUMPRODUCT where the range is generated by a function?

    See if this works -

    =SUMPRODUCT(--(MONTH(Quotes!$K$2:$K3139)=8),--(YEAR(Quotes!$K$2:$K3139)=2013),Quotes!$T$2:$T3139)

  10. #10
    Registered User
    Join Date
    01-14-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: SUMPRODUCT where the range is generated by a function?

    Thanks Tony Valko! Although there were no text entries in the sum range, this has solved this issue!

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT where the range is generated by a function?

    Do any of the formulas in the sum range return formula blanks "" ?

    If so, formula blanks are text values.

    Glad you got working!

  12. #12
    Registered User
    Join Date
    01-14-2014
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Excel 2013
    Posts
    21

    Re: SUMPRODUCT where the range is generated by a function?

    ah ha! That makes sense!
    Yes, they do contain formula blanks.
    Thanks very much. That would have bugged me all day

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: SUMPRODUCT where the range is generated by a function?

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Combine SUMPRODUCT function with an a range
    By husni in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2014, 11:33 PM
  2. [SOLVED] Evaluate sumproduct function with range variable
    By walduxas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2012, 01:25 AM
  3. Replies: 1
    Last Post: 07-20-2012, 11:34 AM
  4. Replies: 2
    Last Post: 06-08-2012, 07:38 AM
  5. [SOLVED] Dynamically Generated Range using Macro
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2006, 03:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1