Sumproduct with text and numbers in cells

[Lee]

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,

 

[Barb Reinhardt]

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?

 

[Lee]

I have tried something like this:
=sumproduct(--(weeknum(A1:A10,1)=othersheetA1),--(C1:10>0),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]

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

 

[Lee]

=SUMPRODUCT(--(WEEKNUM(Spring4!$N$2:$N$2000,1)='Weekly Plug
Tray'!$A4),--(Spring4!$P$2:$P$2000>0),(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$2000>0),(Spring4!$P$2:$P$2000))

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

 

[T. Valko]

The WEEKNUM function won't work on arrays.

 

[Neophyte]

Thanks for telling me. It will work with the helper column. How do you know
what will and won't work?
Lee

 

[T. Valko]

How do you know what will and won't work?

Experience combined with trial and error. I don't think I've ever read in
the Excel help files that such and such function(s) do not work with arrays.
So, it's trial and error.

 

[daddylonglegs]

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$2000>0),(Spring4!$P$2:$P$2000))

 

[Barb Reinhardt]

Thanks, I've learned something today.

Barb Reinhardt

 

[Lee]

Thanks to all for helping. I don't feel quite so ignorant knowing that all
functions do not get along. (Sounds like some programers built a little of
their persona into them.) Again, many thanks to all.

Lee