Combination of 1/0 with TRUE/FALSE

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
957
Office Version
2007
Platform
Windows
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>
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,043
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:

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
957
Office Version
2007
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!
Thanks Eric. IT WORKS
 
Last edited:

Eric W

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

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

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
957
Office Version
2007
Platform
Windows
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,570
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,807
Messages
5,446,583
Members
405,410
Latest member
NAnsari

This Week's Hot Topics

Top