A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SUMPRODUCT



 
 
Thread Tools Display Modes
  #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.




Ads
  #2  
Old April 25th, 2010, 03:57 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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 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.






  #3  
Old April 26th, 2010, 02:08 AM posted to microsoft.public.excel.worksheet.functions
Skinman
external usenet poster
 
Posts: 13
Default 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 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.







  #4  
Old April 26th, 2010, 03:04 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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 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.









 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.