problem sumproduct ?

**vumian** · 02-15-2008, 12:01 PM

dear all,

i successfully did with {=sum(if...)}

i now wanna the same result with sumproduct, but result is zero

=SUMPRODUCT(--(NOT(TEXT($F$7:$AJ$7,"ddd")="SUN"))*(LEFT((F40:AJ40),1)="A"),RIGHT(F40:AJ40)) -> result is 0

=SUMPRODUCT(--(NOT(TEXT($F$7:$AJ$7,"ddd")="SUN"))*(LEFT((F40:AJ40),1)="A"),value(RIGHT(F40:AJ40))) --> result is #value

thanks for your help,
vumian

**EdMac** · 02-15-2008, 12:41 PM

Hi

Not sure exactly what you are trying to achieve but SUMPRODUCT requires all ranges to be the same size.

Why not post a sample and a description of what you want to achieve?

Ed

**daddylonglegs** · 02-15-2008, 01:04 PM

Hello vumian,

What sort of values do you have in F40:AJ40? Presumably a letter followed by a single digit, e.g. A3?

If so then I imagine that your second formula will work if there are no blanks in the range but it will fail if there are blanks (in F40:AJ40) or if that range contains data where the rightmost character is not a number.

To accommodate blanks perhaps try

=SUMPRODUCT(--(WEEKDAY($F$7:$AJ$7)<>1)*(LEFT(F40:AJ40)="A"), RIGHT(0&F40:AJ40)+0)

**vumian** · 02-15-2008, 09:24 PM

hi Daddylonglegs,

Thanks for your formula, but it does not work

, return #value
F40:AJ40 have Text only (eg : AB or AC), Text and number (eg : A2 or A8), and Blank

Thanks for your help again

**daddylonglegs** · 02-16-2008, 11:28 AM

No, if you have entries like "AC" that formula won't work, presumably if you have "AC" on a non-Sunday it should count as zero?

It's difficult to do with SUMPRODUCT because you need to coerce the RIGHT function to numeric...but if you do that where the cell contains text you get an error.

Why not stick with SUM(IF....?

You could perhaps use

=SUM(IF((WEEKDAY($F$7:$AJ$7)<>1),IF(ISNUMBER( SUBSTITUTE(F40:AJ40,"A",0)+0),SUBSTITUTE(F40:AJ40,"A",0)+0)))

confirmed with CTRL+SHIFT+ENTER