+ Reply to Thread
Results 1 to 16 of 16

SUMPRODUCT Problem with blank non blank cells included in range and ever changing range

  1. #1
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    SUMPRODUCT Problem with blank non blank cells included in range and ever changing range

    Hi

    I have two queries with a SUMPRODUCT formula I'm trying to write:

    1st - to calculate a range but I keep running into trouble due to cells that appear to be blank but are not (demonstrated in the example in cell A7 with a ') and returns a #VALUE. - I have tried a number of things ISBLANK, COUNTA, COUNTBLANK with no joy. SUMPRODUCT formula in Cell D1.

    2nd - this range will vary in column length each time and i need it a simply addition to the SUMPRODUCT formula to take account of this and go down to the last cell with an actual number in column A. Hope i have explained this properly.

    Obviously, report churns out the date in column A as text and it is usually more than the 7 lines i have entered.

    Any help would be great.
    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    Try this out:

    =SUMPRODUCT(IFERROR(VALUE(A1:A7)<40000,0)*IFERROR(VALUE(A1:A7)>20000,0),B1:B7)

    you will need to run as an array (Ctrl+Shift+Enter)

    im not sure i understand your second part though.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    You could try using an "array formula", i.e.

    =SUM(IF(ISNUMBER(A1:A7+0), IF(A1:A7+0>20000,IF(A1:A7+0<40000,B1:B7))))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

  4. #4
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    I've tried both and neither comeback with anything. Also, not sure what you both mean with 'confirmed with CTRL+SHIFT+ENTER'.

    DGagnon - the second part means that at the moment the range is 6 lines, the next time it could be 20, 30, even 200 lines. I just need it to go to the last cell with actual figures in. with the attachment example, that would be line 6 even though line 7 has blank items in it. Hope this explains a little better.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    Quote Originally Posted by argegg26 View Post
    .....not sure what you both mean with 'confirmed with CTRL+SHIFT+ENTER'......
    This is essential for array formulas. Put the formula in a cell, select that cell and press F2 key to select formula.....then hold down CTRL and SHIFT keys and press ENTER. If done correctly then curly braces like { and } will appear around the formula.

    You can extend the ranges to accommodate any expansion, it doesn't matter if some rows are blank, e.g. try

    =SUM(IF(ISNUMBER(A1:A1000+0), IF(A1:A1000+0>20000,IF(A1:A1000+0<40000,B1:B1000))))

  6. #6
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    Thanks Daddylonglegs.

    Like i do with most of my posts, I like to learn these things, why is the f2 and ctrl+shift+enter so vital with an array formulas? also, what is the difference between a normal formula and an array formula?
    Sorry to ask, just so i know so i can apply it in the future.

  7. #7
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    an Array formula will run multipul calculations together, for example, in a standard formula =IF(A1:A2=2,1,0) it will only return one result either a 1 or a 0, with an array formula it will return an array of results {1,1} or {1,0} or {0,1} or {0,0} for example depending on what was entered

    using the above formula if A1 = 3 and A2=2 then the array would return {0,1} because it is true only in the second case, where as a standard formula would only look to the first case, and return a 0.

    you could then do something like a sum on that, which would add the results together, or an average even.

    the array formula of =AVERAGE(IF(A1:A2=2,1,0)) would return .5 in the case above.

    hope this helps.
    Last edited by DGagnon; 04-24-2012 at 03:36 PM.

  8. #8
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    Thanks again DGagnon.

    Would I be right in thinking I could enter the { and } when writing the formula instead of f2, then Ctrl+Shift+enter?

  9. #9
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    No I wouldn't it would appear

  10. #10
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    no, you must use ctrl+Shift+Enter, using the { } wont do anything, it will actualy turn your formula to a text string.

  11. #11
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    And another quick question, why is the +0 so important in the formula? as when I remove it, it does not work and I can't understand why removing 0 would have this effect.

  12. #12
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    it converts the text values returned into #'s which will allow mathmatical comparisons such as > and <

  13. #13
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    so it's a quicker way of using VALUE(...) formula?

  14. #14
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    basicaly, yes.

  15. #15
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    Once again, Thanks DG.

  16. #16
    Registered User
    Join Date
    03-28-2010
    Location
    wokingham, England
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: SUMPRODUCT Problem with blank non blank cells included in range and ever changing rang

    Another quick question. Formulas in columns A and B, ie to return a number between 0 and 40000 and the value in column B. I have tried the above CSE formula with formulas in the ranges and it come back #value. Do you know how I amend/address this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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