SUMPRODUCT problem, using OR within statement
 

My basic problem is I want SUMPRODUCT to have a value of 1 if all the below
conditions are met (ie. if D10 is Analyst Full Time, E10 contains any
text,and M10 is Offer Outstanding the value should be one). Whilst this does
happen, it also counts a value of 1 if anything is filled in column E, even
if the other columns aren't completed at all, it just adds everything up
generally. Can anyone see anything glaringly wrong with my formula?

=SUMPRODUCT(OR($D$10:$D$209="Analyst Full Time",$D$10:$D$209="Associate Full
Time")*(ISTEXT($E$10:$E$209))*(OR($M$10:$M$209="Of fer
Accepted",$M$10:$M$209="Offer Declined",$M$10:$M$209="Offer Outstanding")))

SUMPRODUCT problem, using OR within statement
 

try
=SUMPRODUCT((RIGHT(D10:D12,4)="time")*(LEN(E10:E12 )1)*(LEFT(M10:M12,5)="offer"))

--
Don Guillett
SalesAid Software

"Alex" wrote in message
...
My basic problem is I want SUMPRODUCT to have a value of 1 if all the
below
conditions are met (ie. if D10 is Analyst Full Time, E10 contains any
text,and M10 is Offer Outstanding the value should be one). Whilst this
does
happen, it also counts a value of 1 if anything is filled in column E,
even
if the other columns aren't completed at all, it just adds everything up
generally. Can anyone see anything glaringly wrong with my formula?

=SUMPRODUCT(OR($D$10:$D$209="Analyst Full Time",$D$10:$D$209="Associate
Full
Time")*(ISTEXT($E$10:$E$209))*(OR($M$10:$M$209="Of fer
Accepted",$M$10:$M$209="Offer Declined",$M$10:$M$209="Offer
Outstanding")))

SUMPRODUCT problem, using OR within statement
 

Excellent, works a charm thanks for that.

I have another one which should be less difficult to figure out.

I want to put validation into cells G10:G209 which will only allow a text
entry if F10:F209 is blank. How would I go about that?

"Don Guillett" wrote:

try
=SUMPRODUCT((RIGHT(D10:D12,4)="time")*(LEN(E10:E12 )1)*(LEFT(M10:M12,5)="offer"))

--
Don Guillett
SalesAid Software

"Alex" wrote in message
...
My basic problem is I want SUMPRODUCT to have a value of 1 if all the
below
conditions are met (ie. if D10 is Analyst Full Time, E10 contains any
text,and M10 is Offer Outstanding the value should be one). Whilst this
does
happen, it also counts a value of 1 if anything is filled in column E,
even
if the other columns aren't completed at all, it just adds everything up
generally. Can anyone see anything glaringly wrong with my formula?

=SUMPRODUCT(OR($D$10:$D$209="Analyst Full Time",$D$10:$D$209="Associate
Full
Time")*(ISTEXT($E$10:$E$209))*(OR($M$10:$M$209="Of fer
Accepted",$M$10:$M$209="Offer Declined",$M$10:$M$209="Offer
Outstanding")))