Set Range Macro

[mbarnett]

Hi All,

I have a wksheet that contains 8000 rows. Data in Column A contains Dates. I currently sort the data and have a another spreadsheet do a bunch of sumif's of the data for a paticular date (today -1). My problem is the one day there may be 50 (today -1) and the next 2000 (today-1). So I always have to redine my sumif range. Is it possible to have a macro based on date(today -1) and define the range. My range would be from column A to Coulm T.

Please help if you can.

Mike

 

[Bowers74]

Have you taken a look at the SUMPRODUCT() function for this?

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Bowers74]

If you DID use SUMPRODUCT() for this, you would NEVER have to sort the data again, AND you wouldn't have to worry about how many RecordSets there were for a particular date!

PLUS . . . If you used Dynamic Named Ranges, you would never have to worry about redefining your ranges to match your table, becasue the Dynamic Named Ranges "grow" with the table.

Peace!!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[mbarnett]

Hi,

Thanks for the mail, but how would I used sumProduct.

Say I Have 75 4/28/04 today and 1000 4/28/04 another day and I need to sumif 15 columns in Sheet A from Data in Sheet B. so in Sheet A I would have 4/28/04. How would I use the SumProduct

 

[Chance1234]

=sumproduct(if(A1:A4000 = 1/3/02),B1:B4000)

enter formula as array formula (shift+enter)

the above would sum all the values in b where teh date column in A = 1/3/02

Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[mbarnett]

Hi,

I tried doing it, but excel will not accept the formula.

=sumproduct(if('EOD Spdsht'!A3:A1166=3/31/04),'EOD Spdsht'!K1104:K1166)

Any Idea why. I tried to put an array, but I continue to get problems. Thanks

 

[xlbo]

no array needed but the column lengths MUST be the same
change K1104:K1166 to K3:K1166 and it should work (remember, this is NOT an array formula so just use enter on its own)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question