+ Reply to Thread
Results 1 to 15 of 15

Sumproduct logic array issue

  1. #1
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Sumproduct logic array issue

    What I am trying to do is easy to solve using sumifs and if statements however I am required to solve this alternatively using sumproduct arrays.

    I need to summarise the data below for energy sources across a time series. When the year is between the phase period, it will be divided by the phase number, if not it is simply left alone. Also it is not counted if the start year is not in play yet. See below.

    8hhid.png

    and therefore the solution is easy to summarise

    w5Qwg.png

    This is done easily above using sumifs and if statements as above. However I need/want to solve it via sumproduct array logic means. I tried to do this using

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but it isn't giving me quite the correct answer and i am stumped! feel like i have stretched the limits of my ability here so any assitance would be very useful. aware that the sumproduct approach is not the best way to do this but i still want to figure this one out!

    thanks

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumproduct logic array issue

    if you've solved it with SUMIFS and IF statements, why do you need to do it using sumproduct?
    Also, I don't believe most on this site want to try to type in your data to try to get you the results.
    Look at the yellow banner at the top of the post and follow the instructions to upload a workbook.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumproduct logic array issue

    BTW, depending on how your "years" are structured, using $E$3:$E$14>=year for example really applies to a date like 1/1/2019 unless it is pointing to a named range.
    it "appears" from your snapshot that your years are just actually numbers like 2019, 2020 and if that is the case you can just say greater than or equal to a specific number like 2019 or 2020. Maybe that will help some.

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Sumproduct logic array issue

    Quote Originally Posted by 3foo3 View Post
    What I am trying to do is easy to solve using sumifs and if statements however I am required to solve this alternatively using sumproduct arrays. . . .
    If you mean you have superiors who don't trust or don't understand array formulas, you have a career problem you may eventually be obliged to handle.

    I for one am NOT going to reproduce the screen shots above to test formulas. I will say that

    SUMIFS(range0,range1,"="&condition1,range2,"<>"&condition2) can be replaced by SUMPRODUCT(range0,(range1=condition1)*(range2<>condition2))

    I'll also say that (x)*(1/y) could be shortened (simplified from the perspective of this mathematician) to (x)/(y) if not to x/y. Excessive redundant parentheses do not improve performance, and they detract from rather than improve readability.

  5. #5
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct logic array issue

    No reason, just wanting to see if it's possible. I did say that in my OP as I anticipated such a response

    Will upload a workbook here. Thanks.

  6. #6
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct logic array issue

    Quote Originally Posted by Sam Capricci View Post
    if you've solved it with SUMIFS and IF statements, why do you need to do it using sumproduct?
    Also, I don't believe most on this site want to try to type in your data to try to get you the results.
    Look at the yellow banner at the top of the post and follow the instructions to upload a workbook.
    No reason, just wanting to see if it's possible. I did say that in my OP as I anticipated such a response

    Will upload a workbook here. Thanks.

  7. #7
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct logic array issue

    Quote Originally Posted by hrlngrv View Post
    If you mean you have superiors who don't trust or don't understand array formulas, you have a career problem you may eventually be obliged to handle.

    I for one am NOT going to reproduce the screen shots above to test formulas. I will say that

    SUMIFS(range0,range1,"="&condition1,range2,"<>"&condition2) can be replaced by SUMPRODUCT(range0,(range1=condition1)*(range2<>condition2))

    I'll also say that (x)*(1/y) could be shortened (simplified from the perspective of this mathematician) to (x)/(y) if not to x/y. Excessive redundant parentheses do not improve performance, and they detract from rather than improve readability.
    Thanks for input - you are right. I am freelance so this is purely as I want to know if this is possible. You guys are all thinking too much!

  8. #8
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct logic array issue

    workbook attached.
    Attached Files Attached Files

  9. #9
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumproduct logic array issue

    Well don't take offense for the question, the reason some of us will ask that is because the forum rules suggest we provide assistance by pointing out solutions if this is a homework assignment but if it is work related then most will be glad to assist with correcting formulas.

  10. #10
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct logic array issue

    Quote Originally Posted by Sam Capricci View Post
    Well don't take offense for the question, the reason some of us will ask that is because the forum rules suggest we provide assistance by pointing out solutions if this is a homework assignment but if it is work related then most will be glad to assist with correcting formulas.
    None offence taken I assure you. I am a 30 something consultant who uses excel to build models. This is a problem that peaked my interest and i simply want to know if it is possible to solve via this route.

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumproduct logic array issue

    if you are talking about the sumproduct in cells I2 through R4, it matches the sumif results in G30 through P32 in all instances except 3, J30, K30 and L30 don't match L2, M2 and N2.
    I'm trying to tear apart the formula to see what is causing the differences.
    Is it those 3 that you mean?

  12. #12
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct logic array issue

    Quote Originally Posted by Sam Capricci View Post
    if you are talking about the sumproduct in cells I2 through R4, it matches the sumif results in G30 through P32 in all instances except 3, J30, K30 and L30 don't match L2, M2 and N2.
    I'm trying to tear apart the formula to see what is causing the differences.
    Is it those 3 that you mean?
    That is exactly it Sam yeh.

    I basically don't know enough about how the if/and/or statements work when using sumproduct. I know that * denotes AND logic and + denotes OR logic but putting it all together is confusing to me.

  13. #13
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumproduct logic array issue

    I’ll be offline until tomorrow, hopefully someone will come along before then and spot what I’ve missed so far.

  14. #14
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Sumproduct logic array issue

    The problem in your SUMPRODUCT formulas is this part (redundant parentheses removed to produce greater clarity, and top-row cell address replaced by its value)

    for 2022 in top column
    *(($E$2:$E$13>=2022)/$D$2:$D$13+($E$2:$E$13<=2022)/$F$2:$F$13)

    Specifically, the problem is that these should be mutually exclusive, BUT E8 has value 2022, so both ($E$2:$E$13>=2022) and ($E$2:$E$13<=2022) are true for E8, so 2022 uses 1/4+1, or 1.25 for the corresponding figure in column B. You need to change the <= to <.

  15. #15
    Registered User
    Join Date
    03-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: Sumproduct logic array issue

    Quote Originally Posted by hrlngrv View Post
    The problem in your SUMPRODUCT formulas is this part (redundant parentheses removed to produce greater clarity, and top-row cell address replaced by its value)

    for 2022 in top column
    *(($E$2:$E$13>=2022)/$D$2:$D$13+($E$2:$E$13<=2022)/$F$2:$F$13)

    Specifically, the problem is that these should be mutually exclusive, BUT E8 has value 2022, so both ($E$2:$E$13>=2022) and ($E$2:$E$13<=2022) are true for E8, so 2022 uses 1/4+1, or 1.25 for the corresponding figure in column B. You need to change the <= to <.
    That's it! Wow after trying to figure this out for a full day I can't believe how close I was. Thank you and thanks Sam also for taking a look!

+ 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. [SOLVED] SUMPRODUCT Formula, using OR Logic
    By YUSATrain in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-22-2018, 05:22 PM
  2. Help for SumProduct and Counta in arrays
    By madisi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-20-2016, 10:29 AM
  3. logic behind sumproduct
    By ammartino44 in forum Excel General
    Replies: 4
    Last Post: 09-17-2014, 11:48 AM
  4. [SOLVED] Sumproduct with OR Logic
    By daffodil11 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-17-2014, 09:27 PM
  5. [SOLVED] SUMPRODUCT. using AND-OR logic with text
    By FAL22 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-26-2013, 04:34 PM
  6. Array/Sumproduct issue. Trying to "count" text.
    By purepower in forum Excel General
    Replies: 2
    Last Post: 10-24-2012, 01:44 PM
  7. Excel 2007 : Conditional logic issue
    By voodoodaddy in forum Excel General
    Replies: 6
    Last Post: 04-05-2011, 10:25 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