Combination of 1/0 with TRUE/FALSE

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
Column B2:B7 is EITHER of 1 OR 0
Input Data Range D2:H7 is EITHER of TRUE OR FALSE
Input ‘Header’ (above D2:H7) i.e. D1:H1 is 111, 222, 333, 444 & 555

Output Range J2:N7 as shown:

How to accomplish.
Thanks in advance
I am using Excel 2007
Sheet2

BCDEFGHIJKLMN
11 or 0 111222333444555
21 TRUETRUETRUETRUETRUE 111222333444555
31 FALSEFALSEFALSEFALSEFALSE 00000
40 TRUETRUETRUETRUETRUE 00000
50 FALSEFALSEFALSEFALSEFALSE 00000
61 TRUEFALSETRUEFALSETRUE 11103330555
71 TRUETRUETRUEFALSEFALSE 11122233300

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 41px"><col style="WIDTH: 13px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"><col style="WIDTH: 13px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"><col style="WIDTH: 42px"></colgroup><tbody>
</tbody>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,513
Office Version
365
Platform
Windows
Put this formula in cell J2 and copy to all other cells in J2:N7:
Code:
=$B2*D2*D$1

Edit: Too slow! Eric beat me to the punch!
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,017
Glad we could help! :)

(I saw it just a little sooner, Joe!)
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
Some design changed…..please allow

B2:B5 is 1, 0, 1, 0 as shown
C2:C5 is TRUE, TRUE, FALSE, FALSE as shown

E2:I5 is ‘continuous’ column & rows filled with values

2 INPUT ranges are there:
INPUT range 1: B7:B16 which is EITHER of 1 OR 0
INPUT range 2: E7:I16 which is EITHER of TRUE OR FALSE

OUTPUT RANGE (Formula required): K7:O16 as shown (I think it should be clear)

How to accomplish?
Thanks in advance.
I am using Excel 2007
Sheet3

BCDEFGHIJKLMNO
21TRUE 1020304050
30TRUE 1122334455
41FALSE 111222333444555
50FALSE 11112222333044405550
6
71 TRUETRUETRUETRUETRUE 1020304050
80 TRUETRUETRUETRUETRUE 1122334455
91 TRUEFALSEFALSEFALSETRUE 1022233344450
100 FALSETRUEFALSETRUEFALSE 1111223330445550
11
12
13
14
15
161 TRUETRUETRUEFALSEFALSE 102030444555

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 23px"><col style="WIDTH: 43px"><col style="WIDTH: 15px"><col style="WIDTH: 43px"><col style="WIDTH: 43px"><col style="WIDTH: 43px"><col style="WIDTH: 43px"><col style="WIDTH: 43px"><col style="WIDTH: 13px"><col style="WIDTH: 38px"><col style="WIDTH: 38px"><col style="WIDTH: 38px"><col style="WIDTH: 38px"><col style="WIDTH: 38px"></colgroup><tbody>
</tbody>
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,017
In K7:

=IF($B7="","",SUMPRODUCT(E$2:E$5*($B$2:$B$5=$B7)*($C$2:$C$5=E7)))

Copy down and across as needed.
 

Forum statistics

Threads
1,082,602
Messages
5,366,566
Members
400,903
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top