Using AND and OR together
 

Hello,

I am having issues executing the following properly:

I have a form that will be filled out by an assessor.

If checkbox in D5 is checked (which produces "true" in T5) and a score of 3 is picked from the drop down in E5, then I want D2 to equal 10. I want this to be the same for repetitively down and across to D8 (T8) and E8.

Simply if a box is checked and the score is three in any of those rows (D5 and E5, D6 and E6, D7 and E7, and D8 and E8) then produce a 10 in D2 otherwise produce a 5. Conditionally formatting shows a check if D2 equals 10 and an X is it equals 5.

The rows do not depend on each other, so there will not be a double check for example or anything else if two rows have boxes checked and 3s. I want D2 to stay an X (5) even if all boxes are checked but no threes are entered in column E.

Using AND and OR together
 

Hi Chris,

Am Mon, 20 Jun 2016 20:54:04 +0100 schrieb chrisparker3268:

Simply if a box is checked and the score is three in any of those rows
(D5 and E5, D6 and E6, D7 and E7, and D8 and E8) then produce a 10 in D2
otherwise produce a 5. Conditionally formatting shows a check if D2
equals 10 and an X is it equals 5.

try in D2:
=IF(MAX((E5:E8=3)*(T5:T8=TRUE))=1,10,5)
and insert the array formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

Using AND and OR together
 

Hi Chris,

Am Mon, 20 Jun 2016 23:59:00 +0200 schrieb Claus Busch:

try in D2:
=IF(MAX((E5:E8=3)*(T5:T8=TRUE))=1,10,5)

without array:
=IF(SUMPRODUCT(--(E5:E8=3),--(T5:T8=TRUE))=1,10,5)

Regards
Claus B.
--
Windows10
Office 2016

Using AND and OR together
 

Hi again,

Am Tue, 21 Jun 2016 00:06:17 +0200 schrieb Claus Busch:

try in D2:
=IF(MAX((E5:E8=3)*(T5:T8=TRUE))=1,10,5)

without array:
=IF(SUMPRODUCT(--(E5:E8=3),--(T5:T8=TRUE))=1,10,5)

in Excel version 2013 or newer you can also use:
=IF(COUNTIFS(E5:E8,3,T5:T8,TRUE)=1,10,5)


Regards
Claus B.
--
Windows10
Office 2016