Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SUMIF an Array? (Excel 97 SR2)

    Trying to do a SUMIF on a table of figures... Can someone point me in the right direction please? The following produces 0:

    =SUMIF($A$6:$A$54,H6,View!$D$6:$DD$56)

    The following will work (i.e. Operating on a single column):

    =SUMIF($A$6:$A$54,H6,View!$Q$6:$Q$56)

    What I would ideally like to do is something like:

    =SUMIF($A$6:$A$54,H6,SUMIF(lstDateRange,View!D$4D$4,View!$D$6:$DD$56))

    Which is the same as the first formula above, with the added complexity that I only want to do the SUM for a particular column if the value in colref:4 (It's a date) matches...

    As usual.. all help and advice appreciated.

    Regards
    Peter

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF an Array? (Excel 97 SR2)

    The following ARRAY formula (confirm with ctrl-shift-enter), should do what you want in case 1. Sumif will ONLY add the first column of data if the second range is multicolumned:

    =SUM(IF($A$6:$A$56=H6,View!$D$6:$DD$56))

    For part 2 I am a little confused but I think this is what you are after ARRAY (ctrl-shift-enter to confirm):

    =SUM(IF($A$6:$A$54=H6,index(View!$D$6:$DD$56,0,Mat ch(lstDateRange,View!D$4D$4,0))))

    where I assumed that lstDateRange contains a date value to determine the "columns" to add. H6 essentially will determine the "rows" to add.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF an Array? (Excel 97 SR2)

    Hi There

    Many thanks.. First one works a treat.. Second one needed amending, my original poor explanation of requirements as opposed to a poor solution on your part!

    =SUM(IF($A$6:$A$56=D6,View!$D$6:INDIRECT(ADDRESS(5 6,MATCH($G$5,View!D$4D$4,0)+3,,,"View"))))

    Array entered of course... Is there a neater/more efficient way of writing the above? In essence what I'm doing is SUMming all of the columns up to an including the one that the date matches, $G$5 in the above example.

    In the production version there will be a series of dates, say eight, to perform the above calcs for. What I'll end up with is the total value for D6 up to a series of chronological dates. Simple math will give me the values between my dates. The version shown above will work for me, just curious if there is a better way. MAny thanks again for your help.

    Regards
    Peter

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF an Array? (Excel 97 SR2)

    Another way of writing (it is not really shorter, but it is clearer to me what it means is ARRAY(ctrl-shift-enter):
    =SUM(IF($A$6:$A$56=D6,OFFSET(View!$D$6:$DD$56,0,0, 50,MATCH($G$5,View!$D$4:$DD$4))))

    A shorter way (If you are not looking for a particular "box" of values, but want the entries that are in columns View!D4D4 whose values that are <G5 (this method allows the column to be in ANY order!) is (This is NOT an array formula):
    =SUMPRODUCT(($A$6:$A$56=D6)*(View!$D6:$DD$56)*(($D $4:$DD$4)<=$G$5))

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SUMIF an Array? (Excel 97 SR2)

    Hi Steve

    Thanks... Option 2 seems to be neater, and works.. For some reason, which I can't see, the array formula works for all but one case. It has something to do with the value being looked for in D6 (D14 in cell where it actually errors #N/A). All other variations of the formula returns the correct answer.. Most odd!

    Another oddity.. on your second formula:

    =SUMPRODUCT(($A$6:$A$56=D6)*(View!$D6:$DD$56)*(($D $4:$DD$4)<=$G$5))

    In last part of the multiplication *(($D$4:$DD$4)<=$G$5)) you didn't specifically refer to the "View!" worksheet, but the calculations worked correctly. I've amended this to *((View!$D$4:$DD$4)<=$G$5)) for readability as much as anything else, but was curious as to why it worked without?

    Regards
    Peter

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SUMIF an Array? (Excel 97 SR2)

    OOPS, I forgot the View ( in my Playing spreadsheet everything was on one page)

    It will work if the sheet with the formula has the same values in D4D4 as the View sheet has in D4D4.

    I'm glad it works for you,
    Steve

Posting Permissions

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