SUMPRODUCT help

[Mark D]

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((K99>100%)*(B118=1),K78*'Base Data'!$G$31))

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

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

 

[Mike H]

Mark,

Try this

=SUMPRODUCT((K99>70%)*(K99<100%)*(B118=1)*(K78*'Base data'!$I$31))
--
Mike

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

 

[Bob Phillips]

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,K99>100%),'Base Data'!$G$31,0)


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

 

[Fred Smith]

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