If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




SUMPRODUCT
Hi all
Please. Needing some assistance. Using excel 2007 This formula starting in the first row of my data ranks each share value for the first day (entered in column 'K') =SUMPRODUCT(($AS$6:$AS$13=AS6)*(C6$C$6:$C$13))+1 In column 'AS' is 3 different types of shares numbered as 1,2 or 3 In column 'C' is the value of those shares I wish to expand the formula so that it also ranks each day seperatly. In column 'T', I have the days numbered in order The formula works only for rows 6:13 which is day 1. On day '2' I may have 10 rows of data starting at 'C14' So at present I have to reenter the formula as: =SUM PRODUCT(($AS$14:$AS$23=AS14)*(C14$C$14:$C$23))+1 As this formula is initially entered via a part of a macro (below) and filled down I have to adjust the formula each time a date changes which is tedious. Each day also has a different amount of entries Range("K6").Select ActiveCell.FormulaR1C1 = _ "=SUM PRODUCT((R6C45:R13C45=RC[34])*(RC[8]R6C3:R13C3))+1" Application.Goto Reference:="Sh__Rank" ' Named offset range Selection.FillDown I have googled and tried adding an additional array with no joy Any help with a new formula would be greatly appreciated. Skinman. 
Ads 
#2




SUMPRODUCT
If T2:T100 is the "day" then something like this should work:
=SUMPRODUCT((T$2:T$100=T2)*(AS$2:AS$100=AS2)*(C2C $2:C$100))+1  Biff Microsoft Excel MVP "Skinman" wrote in message ... Hi all Please. Needing some assistance. Using excel 2007 This formula starting in the first row of my data ranks each share value for the first day (entered in column 'K') =SUMPRODUCT(($AS$6:$AS$13=AS6)*(C6$C$6:$C$13))+1 In column 'AS' is 3 different types of shares numbered as 1,2 or 3 In column 'C' is the value of those shares I wish to expand the formula so that it also ranks each day seperatly. In column 'T', I have the days numbered in order The formula works only for rows 6:13 which is day 1. On day '2' I may have 10 rows of data starting at 'C14' So at present I have to reenter the formula as: =SUM PRODUCT(($AS$14:$AS$23=AS14)*(C14$C$14:$C$23))+1 As this formula is initially entered via a part of a macro (below) and filled down I have to adjust the formula each time a date changes which is tedious. Each day also has a different amount of entries Range("K6").Select ActiveCell.FormulaR1C1 = _ "=SUM PRODUCT((R6C45:R13C45=RC[34])*(RC[8]R6C3:R13C3))+1" Application.Goto Reference:="Sh__Rank" ' Named offset range Selection.FillDown I have googled and tried adding an additional array with no joy Any help with a new formula would be greatly appreciated. Skinman. 
#3




SUMPRODUCT
Thanks very much ~ Works a treat.
For some reason my posts do not show on the site. I am posting to microsoft.public.excel.worksheet.functions Skinman "T. Valko" wrote in message ... If T2:T100 is the "day" then something like this should work: =SUMPRODUCT((T$2:T$100=T2)*(AS$2:AS$100=AS2)*(C2C $2:C$100))+1  Biff Microsoft Excel MVP "Skinman" wrote in message ... Hi all Please. Needing some assistance. Using excel 2007 This formula starting in the first row of my data ranks each share value for the first day (entered in column 'K') =SUMPRODUCT(($AS$6:$AS$13=AS6)*(C6$C$6:$C$13))+1 In column 'AS' is 3 different types of shares numbered as 1,2 or 3 In column 'C' is the value of those shares I wish to expand the formula so that it also ranks each day seperatly. In column 'T', I have the days numbered in order The formula works only for rows 6:13 which is day 1. On day '2' I may have 10 rows of data starting at 'C14' So at present I have to reenter the formula as: =SUM PRODUCT(($AS$14:$AS$23=AS14)*(C14$C$14:$C$23))+1 As this formula is initially entered via a part of a macro (below) and filled down I have to adjust the formula each time a date changes which is tedious. Each day also has a different amount of entries Range("K6").Select ActiveCell.FormulaR1C1 = _ "=SUM PRODUCT((R6C45:R13C45=RC[34])*(RC[8]R6C3:R13C3))+1" Application.Goto Reference:="Sh__Rank" ' Named offset range Selection.FillDown I have googled and tried adding an additional array with no joy Any help with a new formula would be greatly appreciated. Skinman. 
#4




SUMPRODUCT
You're welcome. Thanks for the feedback!
 Biff Microsoft Excel MVP "Skinman" wrote in message ... Thanks very much ~ Works a treat. For some reason my posts do not show on the site. I am posting to microsoft.public.excel.worksheet.functions Skinman "T. Valko" wrote in message ... If T2:T100 is the "day" then something like this should work: =SUMPRODUCT((T$2:T$100=T2)*(AS$2:AS$100=AS2)*(C2C $2:C$100))+1  Biff Microsoft Excel MVP "Skinman" wrote in message ... Hi all Please. Needing some assistance. Using excel 2007 This formula starting in the first row of my data ranks each share value for the first day (entered in column 'K') =SUMPRODUCT(($AS$6:$AS$13=AS6)*(C6$C$6:$C$13))+1 In column 'AS' is 3 different types of shares numbered as 1,2 or 3 In column 'C' is the value of those shares I wish to expand the formula so that it also ranks each day seperatly. In column 'T', I have the days numbered in order The formula works only for rows 6:13 which is day 1. On day '2' I may have 10 rows of data starting at 'C14' So at present I have to reenter the formula as: =SUM PRODUCT(($AS$14:$AS$23=AS14)*(C14$C$14:$C$23))+1 As this formula is initially entered via a part of a macro (below) and filled down I have to adjust the formula each time a date changes which is tedious. Each day also has a different amount of entries Range("K6").Select ActiveCell.FormulaR1C1 = _ "=SUM PRODUCT((R6C45:R13C45=RC[34])*(RC[8]R6C3:R13C3))+1" Application.Goto Reference:="Sh__Rank" ' Named offset range Selection.FillDown I have googled and tried adding an additional array with no joy Any help with a new formula would be greatly appreciated. Skinman. 
Thread Tools  
Display Modes  

