Thread: SUMPRODUCT
View Single Post
  #1  
Old April 25th, 2010, 07:49 AM posted to microsoft.public.excel.worksheet.functions
Skinman
external usenet poster
 
Posts: 13
Default 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 re-enter 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.