Sumproduct with text and numbers in cells

  • Thread starter Thread starter Lee
  • Start date Start date
L

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,
 
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?
 
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
 
=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
 
Thanks for telling me. It will work with the helper column. How do you know
what will and won't work?
Lee
 
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.
 
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))
 
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
 
Back
Top