Sumproduct

[texasalynn]

Ok what is wrong with this?

=+SUMPRODUCT(((BASE!$M$3:$M$47<12)*(BASE!$M$3:$M$47>50)*(BASE!$M$3:$M$47<>0.1))*(BASE!$X$3:$X$47))

I have in column various sizes and I need to add all sizes that are
NOT .1
> 50
< 12
add what's in column X

We don't always have all those sizes but still need to get a total. Right now there is no size > 50, so I get 0. But why can't I get a total of the others?

 

[maytas]

Hi,
Put "+" instead of "*"

 

[pgc01]

Hi texasalynn

You are trying to select values that are <12 and >50. Both conditions cannot be true at the same time.

HTH
PGC

 

[pgc01]

Unless you mean OR instead of AND. In that case

EDIT: Bad formula

HTH
PGC

 

[fairwinds]

=SUMPRODUCT(--ISNUMBER(BASE!$M$3:$M$47),(BASE!$M$3:$M$47<12)+(BASE!$M$3:$M$47>50),--(BASE!$M$3:$M$47<>0.1),BASE!$X$3:$X$47)

 

[pgc01]

Hi again

In case you saw the formula in my post, looking at fairwinds' formula I realized see that my interpretation

(NOT .1 ) OR (<12) OR (>50) makes no sense

Fairwinds interpretation makes sense

((<12)) OR (>50)) AND (NOT .1 )

Sorry
PGC

 

[texasalynn]

whew! Thanks Fairwinds! I don't think I've ever had the , and + together in the same formula like that.

Edit:
why did you have the isnumber bit on column M without a condition?? I took it out so my results.

 

[fairwinds]

why did you have the isnumber bit on column M without a condition?

Just to be on the safe side. Else a blank cell in column M would qualify to be included in the sum.