Results 1 to 6 of 6
Thread: SUMIF an Array? (Excel 97 SR2)

20030211, 09:45 #1
 Join Date
 Oct 2002
 Location
 RossonWye, 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

20030211, 10:31 #2
 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 ctrlshiftenter), 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 (ctrlshiftenter 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

20030211, 14:47 #3
 Join Date
 Oct 2002
 Location
 RossonWye, 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

20030211, 16:29 #4
 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(ctrlshiftenter):
=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

20030211, 17:58 #5
 Join Date
 Oct 2002
 Location
 RossonWye, 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

20030211, 19:06 #6
 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