sumproduct function combined with named range?

A

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
 
F

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
 
B

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
 
A

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
 
M

Mark Graesser

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
 
B

Bob Phillips

Mark Graesser said:
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
Click to expand...
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!
 
A

Alan

Many thanks... these are great solutions!
Alan
Bob Phillips said:
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!
Click to expand...
 
M

Mark Graesser

Bob
Thanks for the accolades

Mark Graesse
[email protected]

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


Mark Graesser said:
AlanN
Another option is to change the formula
Click to expand...
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
 
Top