Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

Hi, Thanks for the reply!

When using =Datevalue(G2) however, it also gives a value error!

I'm going home now, so i won't read it untill tomorrow. Thanks for any effort.

"Joel" wrote:

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

What values are in G2?

"Gunti" wrote:

Hi, Thanks for the reply!

When using =Datevalue(G2) however, it also gives a value error!

I'm going home now, so i won't read it untill tomorrow. Thanks for any effort.

"Joel" wrote:

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

Column G is a list of dates in xx-xx-xxxx format
Column A is a list of names
Column E is a list of Prices

I want to create a list with the following:

Name 'Total amount of Money in week x' by name.


"Joel" wrote:

What values are in G2?

"Gunti" wrote:

Hi, Thanks for the reply!

When using =Datevalue(G2) however, it also gives a value error!

I'm going home now, so i won't read it untill tomorrow. Thanks for any effort.

"Joel" wrote:

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

You dates are in Column G and your original code was referencing Column A.
You also need a 1 in weeknum to indicate the start of week being sunday

SUMPRODUCT(--($A$2:$A$4="Name"),--(WEEKNUM($G$2:$G$4)=Weeknum(DateValue("10/20/08"),1)),$E$2:$E$4)


"Gunti" wrote:

Column G is a list of dates in xx-xx-xxxx format
Column A is a list of names
Column E is a list of Prices

I want to create a list with the following:

Name 'Total amount of Money in week x' by name.


"Joel" wrote:

What values are in G2?

"Gunti" wrote:

Hi, Thanks for the reply!

When using =Datevalue(G2) however, it also gives a value error!

I'm going home now, so i won't read it untill tomorrow. Thanks for any effort.

"Joel" wrote:

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

It's still not working, can i send you an example sheet maybe?

Greetz

"Joel" wrote:

You dates are in Column G and your original code was referencing Column A.
You also need a 1 in weeknum to indicate the start of week being sunday

SUMPRODUCT(--($A$2:$A$4="Name"),--(WEEKNUM($G$2:$G$4)=Weeknum(DateValue("10/20/08"),1)),$E$2:$E$4)


"Gunti" wrote:

Column G is a list of dates in xx-xx-xxxx format
Column A is a list of names
Column E is a list of Prices

I want to create a list with the following:

Name 'Total amount of Money in week x' by name.


"Joel" wrote:

What values are in G2?

"Gunti" wrote:

Hi, Thanks for the reply!

When using =Datevalue(G2) however, it also gives a value error!

I'm going home now, so i won't read it untill tomorrow. Thanks for any effort.

"Joel" wrote:

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

joel dot warburg at itt dot com

"Gunti" wrote:

It's still not working, can i send you an example sheet maybe?

Greetz

"Joel" wrote:

You dates are in Column G and your original code was referencing Column A.
You also need a 1 in weeknum to indicate the start of week being sunday

SUMPRODUCT(--($A$2:$A$4="Name"),--(WEEKNUM($G$2:$G$4)=Weeknum(DateValue("10/20/08"),1)),$E$2:$E$4)


"Gunti" wrote:

Column G is a list of dates in xx-xx-xxxx format
Column A is a list of names
Column E is a list of Prices

I want to create a list with the following:

Name 'Total amount of Money in week x' by name.


"Joel" wrote:

What values are in G2?

"Gunti" wrote:

Hi, Thanks for the reply!

When using =Datevalue(G2) however, it also gives a value error!

I'm going home now, so i won't read it untill tomorrow. Thanks for any effort.

"Joel" wrote:

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,

For people who have the same problem. I solved the problem defining week 42
as a value between 31-12-2007 + (#week#*7) and 31-12-2007+ (%week%*7)-6

"Joel" wrote:



joel dot warburg at itt dot com

"Gunti" wrote:

It's still not working, can i send you an example sheet maybe?

Greetz

"Joel" wrote:

You dates are in Column G and your original code was referencing Column A.
You also need a 1 in weeknum to indicate the start of week being sunday

SUMPRODUCT(--($A$2:$A$4="Name"),--(WEEKNUM($G$2:$G$4)=Weeknum(DateValue("10/20/08"),1)),$E$2:$E$4)


"Gunti" wrote:

Column G is a list of dates in xx-xx-xxxx format
Column A is a list of names
Column E is a list of Prices

I want to create a list with the following:

Name 'Total amount of Money in week x' by name.


"Joel" wrote:

What values are in G2?

"Gunti" wrote:

Hi, Thanks for the reply!

When using =Datevalue(G2) however, it also gives a value error!

I'm going home now, so i won't read it untill tomorrow. Thanks for any effort.

"Joel" wrote:

"Week" need to be a datevalue.

WEEKNUM($A$2:$A$4)=Weeknum(DateValue("10/20/08")).



"Gunti" wrote:

Hello,
I have a list which resembles the following example:
A B C
<Name <Price <Date
<Name2 <Price2 <Date2
<Name3 <Price3 <Date3
The dates are in normal date formats (00-00-0000)
I want Excel to sum up the Prices where which contain the name & week i
insert..

For this i want to use the following formula:
SUMPRODUCT(--($A$2:$A$4="Name"),--( ??? ),$A$2:$A$4)

My question is what to insert at the questionmarks. I was thinking about
using WEEKNUM($A$2:$A$4)="Week". I get the 'VALUE' error!

Any help greatly appreciated.

Greetz,