SUMPRODUCT help
 

Good afternoon everyone

I have the following 2 formula's that are working perfectly for me

=(SUMPRODUCT((K99<70%)*(B118=1),K78*'Base Data'!$I$31))
=(SUMPRODUCT((K99100%)*(B118=1),K78*'Base Data'!$G$31))

I need one last formula that allows me to say if K9970% but less than 100%

Can anybody offer any help, I can't seem to enter it without getting an error

SUMPRODUCT help
 

Mark,

Try this

=SUMPRODUCT((K9970%)*(K99<100%)*(B118=1)*(K78*'Ba se data'!$I$31))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Mark D" wrote:

Good afternoon everyone

I have the following 2 formula's that are working perfectly for me

=(SUMPRODUCT((K99<70%)*(B118=1),K78*'Base Data'!$I$31))
=(SUMPRODUCT((K99100%)*(B118=1),K78*'Base Data'!$G$31))

I need one last formula that allows me to say if K9970% but less than 100%

Can anybody offer any help, I can't seem to enter it without getting an error

SUMPRODUCT help
 

Why are you using SUMPRODUCT for a single cell.

Use

=IF(AND(B118=1,K99<70%),'Base Data'!$I$31,0)

=IF(AND(B118=1,K99100%),'Base Data'!$G$31,0)


the third would follow the same principles, but I am not sure wheter it
would output I31 or G31


--

HTH

Bob

"Mark D" wrote in message
...
Good afternoon everyone

I have the following 2 formula's that are working perfectly for me

=(SUMPRODUCT((K99<70%)*(B118=1),K78*'Base Data'!$I$31))
=(SUMPRODUCT((K99100%)*(B118=1),K78*'Base Data'!$G$31))

I need one last formula that allows me to say if K9970% but less than
100%

Can anybody offer any help, I can't seem to enter it without getting an
error

SUMPRODUCT help
 

Try this:
=k78*if(b118<1,0,if(k99<70%,'Base Data'!$I$31,if(K99<100%,"whatever cell
you want",'Base Data'!$G$31)))

Regards,
Fred

"Mark D" wrote in message
...
Good afternoon everyone

I have the following 2 formula's that are working perfectly for me

=(SUMPRODUCT((K99<70%)*(B118=1),K78*'Base Data'!$I$31))
=(SUMPRODUCT((K99100%)*(B118=1),K78*'Base Data'!$G$31))

I need one last formula that allows me to say if K9970% but less than
100%

Can anybody offer any help, I can't seem to enter it without getting an
error