Sumproduct with text and numbers in cells
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
|