How to if/then cells across multiple columns

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Hello. I am asking for your help to develop a formula that if cells across multiple columns BP, BS, BT are equal "1" then to place the contents from the corresponding cell in column CQ in column BU. Thank you in advance for your help.

2019-22 Media Clips v04.xlsx
BPBQBRBSBTBUBV
1114 Sober Skipper Program55,648,161 Sober Skipper ImpressionsSober Skipper Impressions Valued at $5,192,31999 North American Sober Skipper Advisory Council102 National Boating Industry Safety Awards
2
300
400
500
600
700
800
900
1000
1100
1200
1318500447
14197587511
151122199711
16129462911
17142096642
181182196311
1900
2000
2100
22121182348
2300
24152022
2500
2618500934
2700
28
2900
3000
3100
3200
3300
3400
3500
3600
37176015
3800
3900
4000
4100
4200
4316495547
4400
4500
4600
4700
4800
4918955338
50112072608
5100
521850040311
5317311659
5400
5518274439
5600
5700
5800
591241596943864874
6000
6100
6200
6300
6400
6500
6600
6700
681212226011
6912561911
7000
7100
7200
7300
7400
7500
7600
7700
7800
7900
8000
8100
8200
831294636
84125611911
850011
860011
87110892011
8813492356911
8911394917111
90158579811
91185002011
921140015411
931181713911
9412122211211
951119974111
961888516266812411
97185772811
98149073911
991128813161311
1001100012911
101112071811
10219188311689911
1031173341870811
104144595511
10511089855311
10617713481330511
1071545885893811
10819541811
109132072811
1101167054270911
11115566201516711
11216792555311
113158178114311
114115559211
115132405511
1161109719211
117119064844109611
11814203133711
119121373711
120112281811
121117181811
1221185322117911
123195572811
12416763711
1251114172811
126112751811
127112251811
128126253711
129145674165911
130116687411
13115681811
13216321011
13315062311688111
134136371811
1351619886611348311
1361668479511
13719543711
1381685070742711
139114932605300111
140155572811
141126773711
14216963601702811
1431637722890111
1441196068434911
1451516945323634711
146143513711
147160821811
1481159102654211
1491100020311
15012556151611
151167372811
15216516251243911
153185001022211
154164941081
1551850033711
15600
157110891011
15800
15900
16000
16100
16200
16300
16400
16500
16600
16700
16800
16900
17000
17100
172
17300
17400
17500
17600
17700
178
179
18013552413740
181136335901311
18214209691811
18300
18400
18500
18600
18700
18800
18900
19000
19118500898
19200
19300
194114692111
1951140907411
1961125075311
19700
1981850045911
19900
20000
20100
20200
2031182197711
20400
20500
20600
20700
20800
20900
21000
21100
21200
21300
21400
21500
21600
21700
218130419522
21914563262
22000
22100
22200
22300
22400
22500
22600
22700
22800
22900
23000
23100
23200
23300
23400
23500
23600
23700
23800
23900
24000
24100
24200
24300
24400
24500
24600
24700
24800
24900
25000
25100
25200
253139654211
254133723711
255146983711
2561850039211
25700
25800
259133722811
26016056111
26100
26200
26300
26400
26500
26600
26700
26800
26900
27000
27100
27200
27300
27400
27500
27600
27700
27800
27900
28000
28100
28200
28300
28400
28500
28600
28700
28800
28900
29000
29100
29200
29300
29400
29500
29600
29700
29800
29900
30000
30100
30200
30300
30400
30500
30600
30700
30800
30900
31000
31100
31200
31300
31400
31500
31600
31700
31800
31900
32000
32100
32216056311
32311
324133722911
32500
32600
32700
32800
32900
33000
33100
33200
33300
33400
335001
336001
33700
33800
339115740711
340133725511
341130341211
34200
343158906111
raw
Cell Formulas
RangeFormula
BP1BP1=CONCATENATE(SUM(BP2:BP1780), " Sober Skipper Program")
BQ1BQ1=CONCATENATE(TEXT(SUM(BQ2:BQ1420),"#,##0")," Sober Skipper Impressions")
BR1BR1=CONCATENATE("Sober Skipper Impressions Valued at $",TEXT(SUM(BR2:BR2010),"#,##0"))
BS1BS1=CONCATENATE(SUM(BS2:BS1780), " North American Sober Skipper Advisory Council")
BT1BT1=CONCATENATE(SUM(BT2:BT1780), " National Boating Industry Safety Awards")
BQ3:BQ27,BQ324:BQ343,BQ180:BQ322,BQ173:BQ177,BQ29:BQ171BQ3=IF(BP3=1,CQ3,"0")
BR3:BR27,BR324:BR343,BR180:BR322,BR173:BR177,BR29:BR171BR3=IF(BP3=1,CR3,"0")
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

aghaffar82

Board Regular
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Try this in cell BU3:
=IF(AND(BP3=1,BS3=1,BT3=1),CQ3,"")
 

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Try this in cell BU3:
=IF(AND(BP3=1,BS3=1,BT3=1),CQ3,"")
Hello. Does not work when cells are blank- tried on line 335 and only worked with all the cells contained "1" - but I appreciate your suggestion.
 

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Hello. Does not work when cells are blank- tried on line 335 and only worked with all the cells contained "1" - but I appreciate your suggestion.
I just changed AND to OR and your suggestion worked! -Thank you.
 
Solution

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

I just changed AND to OR and your suggestion worked! -Thank you.
Might you know a way to write this formula to include a range of cells across multiple columns such as BP3-BT3 rather than noting each cell in the same row?
 

aghaffar82

Board Regular
Joined
Jun 13, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
AND means if all cells in the given columns i.e. BP, BS & BT have 1 in them, and OR means any of them has 1 in them. I used AND because your initial requirement was
"multiple columns BP, BS, BT are equal "1" then to place the contents"
I just changed AND to OR and your suggestion worked! -Than
 

wmichael

Board Regular
Joined
Aug 26, 2014
Messages
103
Office Version
  1. 365
  2. 2019
  3. 2007
Platform
  1. Windows
  2. Mobile
Might you know a way to write this formula to include a range of cells across multiple columns such as BP3-BT3 rather than noting each cell in the same row?
Just figured it out
IF(OR(BZ336:CF336=1),CR336,"")
Thanks again!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,695
Messages
5,626,357
Members
416,174
Latest member
LavendarRabbit

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
Top