Excel 2007

I want to sum a column that each cell has a number or text in it based on
the conditions of that cell having a number and the results of other arrays.
I keep getting #value error even though I use the double unary (--). Can I
do this using sumproduct or do I need to use the sumifs? I was hoping to use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman

What exactly are you trying?

=SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10))

Sums values in column C where A = "Text" and b (the value) = 1.

Is this what you're doing?
--
HTH,
Barb Reinhardt



"Lee" wrote:

Excel 2007

I want to sum a column that each cell has a number or text in it based on
the conditions of that cell having a number and the results of other arrays.
I keep getting #value error even though I use the double unary (--). Can I
do this using sumproduct or do I need to use the sumifs? I was hoping to use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman

I have tried something like this:
=sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:100),C1:C10)
I want to sum column C based on the date column A equal to the week number
in A1 on another sheet. The problem is the C column has numbers or text, not
both, and I want to sum the numbers in the C column that are greater than 0.
I get the #value error based on the above formula.
Thanks,
Lee

"Barb Reinhardt" wrote in message
...
What exactly are you trying?

=SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10))

Sums values in column C where A = "Text" and b (the value) = 1.

Is this what you're doing?
--
HTH,
Barb Reinhardt



"Lee" wrote:

Excel 2007

I want to sum a column that each cell has a number or text in it based on
the conditions of that cell having a number and the results of other
arrays.
I keep getting #value error even though I use the double unary (--). Can
I
do this using sumproduct or do I need to use the sumifs? I was hoping to
use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman

=sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C100),(C1:C10))

--
HTH,
Barb Reinhardt



"Lee" wrote:

I have tried something like this:
=sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:100),C1:C10)
I want to sum column C based on the date column A equal to the week number
in A1 on another sheet. The problem is the C column has numbers or text, not
both, and I want to sum the numbers in the C column that are greater than 0.
I get the #value error based on the above formula.
Thanks,
Lee

"Barb Reinhardt" wrote in message
...
What exactly are you trying?

=SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10))

Sums values in column C where A = "Text" and b (the value) = 1.

Is this what you're doing?
--
HTH,
Barb Reinhardt



"Lee" wrote:

Excel 2007

I want to sum a column that each cell has a number or text in it based on
the conditions of that cell having a number and the results of other
arrays.
I keep getting #value error even though I use the double unary (--). Can
I
do this using sumproduct or do I need to use the sumifs? I was hoping to
use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman

=SUMPRODUCT(--(WEEKNUM(Spring4!$N$2:$N$2000,1)='Weekly Plug
Tray'!$A4),--(Spring4!$P$2:$P$20000),(Spring4!$P$2:$P$2000))

The above formula is what I have and doesn't work. However, if I use the
formula below with a helper column it works:

=SUMPRODUCT(--(Spring4!$U$2:$U$2000='Weekly Plug
Tray'!$A5),--(Spring4!$P$2:$P$20000),(Spring4!$P$2:$P$2000))

My problem may be with the weeknum function rather than with the sumproduct.
any thoughts?
Thanks again.
Lee

"Barb Reinhardt" wrote in message
...
=sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C100),(C1:C10))

--
HTH,
Barb Reinhardt



"Lee" wrote:

I have tried something like this:
=sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:100),C1:C10)
I want to sum column C based on the date column A equal to the week
number
in A1 on another sheet. The problem is the C column has numbers or text,
not
both, and I want to sum the numbers in the C column that are greater than
0.
I get the #value error based on the above formula.
Thanks,
Lee

"Barb Reinhardt" wrote in
message
...
What exactly are you trying?

=SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10))

Sums values in column C where A = "Text" and b (the value) = 1.

Is this what you're doing?
--
HTH,
Barb Reinhardt



"Lee" wrote:

Excel 2007

I want to sum a column that each cell has a number or text in it based
on
the conditions of that cell having a number and the results of other
arrays.
I keep getting #value error even though I use the double unary (--).
Can
I
do this using sumproduct or do I need to use the sumifs? I was hoping
to
use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman

A helper column would be the easiest way to go, I think, but if you really
wanted to avoid that......

You can replicate =WEEKNUM(A1,1) with

=INT((A1-DATE(YEAR(A1),1,1)-WEEKDAY(A1))/7)+2

so you could change your formula to:

=SUMPRODUCT(--(INT((Spring4!$N$2:$N$2000-DATE(YEAR(Spring4!$N$2:$N$2000),1,1)-WEEKDAY(Spring4!$N$2:$N$2000))/7)+2='Weekly
Plug Tray'!$A4),--(Spring4!$P$2:$P$20000),(Spring4!$P$2:$P$2000))

"Lee" wrote:

=SUMPRODUCT(--(WEEKNUM(Spring4!$N$2:$N$2000,1)='Weekly Plug
Tray'!$A4),--(Spring4!$P$2:$P$20000),(Spring4!$P$2:$P$2000))

The above formula is what I have and doesn't work. However, if I use the
formula below with a helper column it works:

=SUMPRODUCT(--(Spring4!$U$2:$U$2000='Weekly Plug
Tray'!$A5),--(Spring4!$P$2:$P$20000),(Spring4!$P$2:$P$2000))

My problem may be with the weeknum function rather than with the sumproduct.
any thoughts?
Thanks again.
Lee

"Barb Reinhardt" wrote in message
...
=sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C100),(C1:C10))

--
HTH,
Barb Reinhardt



"Lee" wrote:

I have tried something like this:
=sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:100),C1:C10)
I want to sum column C based on the date column A equal to the week
number
in A1 on another sheet. The problem is the C column has numbers or text,
not
both, and I want to sum the numbers in the C column that are greater than
0.
I get the #value error based on the above formula.
Thanks,
Lee

"Barb Reinhardt" wrote in
message
...
What exactly are you trying?

=SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10))

Sums values in column C where A = "Text" and b (the value) = 1.

Is this what you're doing?
--
HTH,
Barb Reinhardt



"Lee" wrote:

Excel 2007

I want to sum a column that each cell has a number or text in it based
on
the conditions of that cell having a number and the results of other
arrays.
I keep getting #value error even though I use the double unary (--).
Can
I
do this using sumproduct or do I need to use the sumifs? I was hoping
to
use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman

The WEEKNUM function won't work on arrays.

--
Biff
Microsoft Excel MVP


"Barb Reinhardt" wrote in message
...
=sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C100),(C1:C10))

--
HTH,
Barb Reinhardt



"Lee" wrote:

I have tried something like this:
=sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:100),C1:C10)
I want to sum column C based on the date column A equal to the week
number
in A1 on another sheet. The problem is the C column has numbers or text,
not
both, and I want to sum the numbers in the C column that are greater than
0.
I get the #value error based on the above formula.
Thanks,
Lee

"Barb Reinhardt" wrote in
message
...
What exactly are you trying?

=SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10))

Sums values in column C where A = "Text" and b (the value) = 1.

Is this what you're doing?
--
HTH,
Barb Reinhardt



"Lee" wrote:

Excel 2007

I want to sum a column that each cell has a number or text in it based
on
the conditions of that cell having a number and the results of other
arrays.
I keep getting #value error even though I use the double unary (--).
Can
I
do this using sumproduct or do I need to use the sumifs? I was hoping
to
use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman

Thanks for telling me. It will work with the helper column. How do you know
what will and won't work?
Lee
"T. Valko" wrote in message
...
The WEEKNUM function won't work on arrays.

--
Biff
Microsoft Excel MVP


"Barb Reinhardt" wrote in
message ...
=sumproduct(--(weeknum(A1:A10,1)=othersheet!A1),--(C1:C100),(C1:C10))

--
HTH,
Barb Reinhardt



"Lee" wrote:

I have tried something like this:
=sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:100),C1:C10)
I want to sum column C based on the date column A equal to the week
number
in A1 on another sheet. The problem is the C column has numbers or text,
not
both, and I want to sum the numbers in the C column that are greater
than 0.
I get the #value error based on the above formula.
Thanks,
Lee

"Barb Reinhardt" wrote in
message
...
What exactly are you trying?

=SUMPRODUCT(--(A1:A10="Text"),--(B1:B10=1),(C1:C10))

Sums values in column C where A = "Text" and b (the value) = 1.

Is this what you're doing?
--
HTH,
Barb Reinhardt



"Lee" wrote:

Excel 2007

I want to sum a column that each cell has a number or text in it
based on
the conditions of that cell having a number and the results of other
arrays.
I keep getting #value error even though I use the double unary (--).
Can
I
do this using sumproduct or do I need to use the sumifs? I was hoping
to
use
the sumproduct to keep the workbook compatible with 2003 user.
Thanks,
--
Lee Coleman