sumproduct function combined with named range?

[AlanN]

In my scheduling project (Excel 2002), I am creating cell dropdown validation to enter in work and time codes. A calculation I have to deal with the data (thanks to other newsgroup gurus!) is this: =SUMPRODUCT((J42:J407={"E","D","N","A","S","U","M","B","V","ST"})*($A$42:$A$407<TODAY())). In column A starting at J42 I have consecutive dates by day starting with 01/01/2004. column J is where the work codes are entered for the first employee and so on in consecutive columns for other employees.

I want to have the ability to add new codes in future without having to edit the calculation manually each time. I have trie to put a named range table in place of the unique codes in the calculation above, but it won't work. Is it possible to insert a named range into a sumproduct command? If so what syntax would I need?

TIA, Alan

 

[Frank Kabel]

Hi Alan
one way
define a name. e.g. test_criteria and enter the following formula for
this name:
={"E","D","N","A","S","U","M","B","V","ST"}

now change your formula to
=SUMPRODUCT((J42:J407=test_criteria)*($A$42:$A$407<TODAY()))

Now you just have to change the name definition

HTH
Frank

 

[Bob Phillips]

Alan,

You can do it, but I am not sure this makes it any easier.

Go into Insert>Name>Define... and add a name of say Codes, and a Refers To value of ={"E","D","N","A","S","U","M","B","V","ST"}, and OK that. You can then use

=SUMPRODUCT((J42:J407=Codes)*($A$42:$A$407<TODAY()))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

In my scheduling project (Excel 2002), I am creating cell dropdown validation to enter in work and time codes. A calculation I have to deal with the data (thanks to other newsgroup gurus!) is this: =SUMPRODUCT((J42:J407={"E","D","N","A","S","U","M","B","V","ST"})*($A$42:$A$407<TODAY())). In column A starting at J42 I have consecutive dates by day starting with 01/01/2004. column J is where the work codes are entered for the first employee and so on in consecutive columns for other employees.

I want to have the ability to add new codes in future without having to edit the calculation manually each time. I have trie to put a named range table in place of the unique codes in the calculation above, but it won't work. Is it possible to insert a named range into a sumproduct command? If so what syntax would I need?

TIA, Alan

 

[Aladin Akyurek]

A faster working formulation would be:

=SUMPRODUCT(--ISNUMBER(MATCH($J$42:$J$407,{"E","D","N","A","S","U","M","B","
V","ST"},0)),--($A$42:$A$407<TODAY()))

If you put the members of {"E","D","N","A","S","U","M","B","V","ST"} in a
range and name the range (preferably: by using a dynamic formula), you can
have:

=SUMPRODUCT(--ISNUMBER(MATCH($J$42:$J$407,Codes,0)),--($A$42:$A$407<TODAY())
)

BTW, if you have empty cells in A42:A407, you need to expand the formula
with an additional conditional:

--($A$42:$A$407<>"")

In my scheduling project (Excel 2002), I am creating cell dropdown
validation to enter in work and time codes. A calculation I have to deal
with the data (thanks to other newsgroup gurus!) is this:
=SUMPRODUCT((J42:J407={"E","D","N","A","S","U","M","B","V","ST"})*($A$42:$A$
407<TODAY())). In column A starting at J42 I have consecutive dates by day
starting with 01/01/2004. column J is where the work codes are entered for
the first employee and so on in consecutive columns for other employees.

I want to have the ability to add new codes in future without having to edit
the calculation manually each time. I have trie to put a named range table
in place of the unique codes in the calculation above, but it won't work. Is
it possible to insert a named range into a sumproduct command? If so what
syntax would I need?

TIA, Alan

 

[Guest]

AlanN
Another option is to change the formula

=SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A$42:$A$407<TODAY())

The area named Fred needs to be in a single column or a single row to work properly. You could also use a range reference to another area in your workbook instead. The range reference or named range could include blank cells to allow you to add new letters without having to reset the range

You code add a worksheet named "Codes" and use the following formula

=SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Codes!A1:A100,0)))*($A$42:$A$407<TODAY())

Now you can have upto 100 codes before you need to change any formulas

Good Luck
Mark Graesse

----- AlanN wrote: ----

In my scheduling project (Excel 2002), I am creating cell dropdown validation to enter in work and time codes. A calculation I have to deal with the data (thanks to other newsgroup gurus!) is this: =SUMPRODUCT((J42:J407={"E","D","N","A","S","U","M","B","V","ST"})*($A$42:$A$407<TODAY())). In column A starting at J42 I have consecutive dates by day starting with 01/01/2004. column J is where the work codes are entered for the first employee and so on in consecutive columns for other employees

I want to have the ability to add new codes in future without having to edit the calculation manually each time. I have trie to put a named range table in place of the unique codes in the calculation above, but it won't work. Is it possible to insert a named range into a sumproduct command? If so what syntax would I need

TIA, Alan

 

[Bob Phillips]

AlanN,
Another option is to change the formula:

=SUMPRODUCT((ISNUMBER(MATCH(J42:J407,Fred,0)))*($A$42:$A$407<TODAY()))

The area named Fred needs to be in a single column or a single row to work

properly.

That's the one! The workbook name is only more flexible if there are
multiple SUMPRODUCT formulae, whereas adding to a column is much easier than
updating even just one formula.

Nice one Mark!

 

[Alan]

Many thanks... these are great solutions!
Alan

work
properly.

That's the one! The workbook name is only more flexible if there are
multiple SUMPRODUCT formulae, whereas adding to a column is much easier than
updating even just one formula.

Nice one Mark!

 

[Guest]

Bob
Thanks for the accolades

Mark Graesse
(e-mail address removed)

----- Bob Phillips wrote: ----

AlanN
Another option is to change the formula

properly

That's the one! The workbook name is only more flexible if there ar
multiple SUMPRODUCT formulae, whereas adding to a column is much easier tha
updating even just one formula

Nice one Mark

 

[Guest]

Alan
Your welcome. Always happy to help. The more interesting the better

Regards
Mark Graesse
(e-mail address removed)