Combination of 1/0 with TRUE/FALSE

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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:
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top