Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 2
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 12:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"