How to if/then cells across multiple columns

[wmichael]

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
	BP	BQ	BR	BS	BT	BU	BV
1	114 Sober Skipper Program	55,648,161 Sober Skipper Impressions	Sober Skipper Impressions Valued at $5,192,319	99 North American Sober Skipper Advisory Council	102 National Boating Industry Safety Awards
2
3		0	0
4		0	0
5		0	0
6		0	0
7		0	0
8		0	0
9		0	0
10		0	0
11		0	0
12		0	0
13	1	8500	447
14	1	9758	75	1	1
15	1	12219	97	1	1
16	1	2946	29	1	1
17	1	42096	642
18	1	18219	63	1	1
19		0	0
20		0	0
21		0	0
22	1	21182	348
23		0	0
24	1	520	22
25		0	0
26	1	8500	934
27		0	0
28
29		0	0
30		0	0
31		0	0
32		0	0
33		0	0
34		0	0
35		0	0
36		0	0
37	1	760	15
38		0	0
39		0	0
40		0	0
41		0	0
42		0	0
43	1	649	5547
44		0	0
45		0	0
46		0	0
47		0	0
48		0	0
49	1	8955	338
50	1	12072	608
51		0	0
52	1	8500	403	1	1
53	1	7311	659
54		0	0
55	1	8274	439
56		0	0
57		0	0
58		0	0
59	1	24159694	3864874
60		0	0
61		0	0
62		0	0
63		0	0
64		0	0
65		0	0
66		0	0
67		0	0
68	1	21222	60	1	1
69	1	2561	9	1	1
70		0	0
71		0	0
72		0	0
73		0	0
74		0	0
75		0	0
76		0	0
77		0	0
78		0	0
79		0	0
80		0	0
81		0	0
82		0	0
83	1	2946	36
84	1	2561	19	1	1
85		0	0	1	1
86		0	0	1	1
87	1	1089	20	1	1
88	1	34923	569	1	1
89	1	13949	171	1	1
90	1	585	798	1	1
91	1	8500	20	1	1
92	1	1400	154	1	1
93	1	18171	39	1	1
94	1	21222	112	1	1
95	1	11997	41	1	1
96	1	8885162	668124	1	1
97	1	857	728	1	1
98	1	4907	39	1	1
99	1	128813	1613	1	1
100	1	1000	129	1	1
101	1	1207	18	1	1
102	1	918831	16899	1	1
103	1	173341	8708	1	1
104	1	4459	55	1	1
105	1	10898	553	1	1
106	1	771348	13305	1	1
107	1	545885	8938	1	1
108	1	954	18	1	1
109	1	320	728	1	1
110	1	167054	2709	1	1
111	1	556620	15167	1	1
112	1	67925	553	1	1
113	1	58178	1143	1	1
114	1	1555	92	1	1
115	1	3240	55	1	1
116	1	10971	92	1	1
117	1	1906484	41096	1	1
118	1	4203	1337	1	1
119	1	213	737	1	1
120	1	1228	18	1	1
121	1	1718	18	1	1
122	1	185322	1179	1	1
123	1	955	728	1	1
124	1	676	37	1	1
125	1	1141	728	1	1
126	1	1275	18	1	1
127	1	1225	18	1	1
128	1	2625	37	1	1
129	1	45674	1659	1	1
130	1	1668	74	1	1
131	1	568	18	1	1
132	1	63	210	1	1
133	1	506231	16881	1	1
134	1	3637	18	1	1
135	1	6198866	113483	1	1
136	1	668	4795	1	1
137	1	954	37	1	1
138	1	685070	7427	1	1
139	1	1493260	53001	1	1
140	1	555	728	1	1
141	1	267	737	1	1
142	1	696360	17028	1	1
143	1	637722	8901	1	1
144	1	196068	4349	1	1
145	1	5169453	236347	1	1
146	1	4351	37	1	1
147	1	6082	18	1	1
148	1	159102	6542	1	1
149	1	1000	203	1	1
150	1	25561	516	1	1
151	1	673	728	1	1
152	1	651625	12439	1	1
153	1	8500	10222	1	1
154	1	649	4108		1
155	1	8500	337	1	1
156		0	0
157	1	1089	10	1	1
158		0	0
159		0	0
160		0	0
161		0	0
162		0	0
163		0	0
164		0	0
165		0	0
166		0	0
167		0	0
168		0	0
169		0	0
170		0	0
171		0	0
172
173		0	0
174		0	0
175		0	0
176		0	0
177		0	0
178
179
180	1	35524	13740
181	1	36335	9013	1	1
182	1	42096	918	1	1
183		0	0
184		0	0
185		0	0
186		0	0
187		0	0
188		0	0
189		0	0
190		0	0
191	1	8500	898
192		0	0
193		0	0
194	1	1469	21	1	1
195	1	14090	74	1	1
196	1	12507	53	1	1
197		0	0
198	1	8500	459	1	1
199		0	0
200		0	0
201		0	0
202		0	0
203	1	18219	77	1	1
204		0	0
205		0	0
206		0	0
207		0	0
208		0	0
209		0	0
210		0	0
211		0	0
212		0	0
213		0	0
214		0	0
215		0	0
216		0	0
217		0	0
218	1	30419	522
219	1	4563	262
220		0	0
221		0	0
222		0	0
223		0	0
224		0	0
225		0	0
226		0	0
227		0	0
228		0	0
229		0	0
230		0	0
231		0	0
232		0	0
233		0	0
234		0	0
235		0	0
236		0	0
237		0	0
238		0	0
239		0	0
240		0	0
241		0	0
242		0	0
243		0	0
244		0	0
245		0	0
246		0	0
247		0	0
248		0	0
249		0	0
250		0	0
251		0	0
252		0	0
253	1	3965	42	1	1
254	1	3372	37	1	1
255	1	4698	37	1	1
256	1	8500	392	1	1
257		0	0
258		0	0
259	1	3372	28	1	1
260	1	60	561	1	1
261		0	0
262		0	0
263		0	0
264		0	0
265		0	0
266		0	0
267		0	0
268		0	0
269		0	0
270		0	0
271		0	0
272		0	0
273		0	0
274		0	0
275		0	0
276		0	0
277		0	0
278		0	0
279		0	0
280		0	0
281		0	0
282		0	0
283		0	0
284		0	0
285		0	0
286		0	0
287		0	0
288		0	0
289		0	0
290		0	0
291		0	0
292		0	0
293		0	0
294		0	0
295		0	0
296		0	0
297		0	0
298		0	0
299		0	0
300		0	0
301		0	0
302		0	0
303		0	0
304		0	0
305		0	0
306		0	0
307		0	0
308		0	0
309		0	0
310		0	0
311		0	0
312		0	0
313		0	0
314		0	0
315		0	0
316		0	0
317		0	0
318		0	0
319		0	0
320		0	0
321		0	0
322	1	60	563	1	1
323				1	1
324	1	3372	29	1	1
325		0	0
326		0	0
327		0	0
328		0	0
329		0	0
330		0	0
331		0	0
332		0	0
333		0	0
334		0	0
335		0	0		1
336		0	0		1
337		0	0
338		0	0
339	1	157	407	1	1
340	1	3372	55	1	1
341	1	3034	12	1	1
342		0	0
343	1	5890	61	1	1
raw

Cell Formulas
Range	Formula
BP1	BP1	=CONCATENATE(SUM(BP2:BP1780), " Sober Skipper Program")
BQ1	BQ1	=CONCATENATE(TEXT(SUM(BQ2:BQ1420),"#,##0")," Sober Skipper Impressions")
BR1	BR1	=CONCATENATE("Sober Skipper Impressions Valued at $",TEXT(SUM(BR2:BR2010),"#,##0"))
BS1	BS1	=CONCATENATE(SUM(BS2:BS1780), " North American Sober Skipper Advisory Council")
BT1	BT1	=CONCATENATE(SUM(BT2:BT1780), " National Boating Industry Safety Awards")
BQ3:BQ27,BQ324:BQ343,BQ180:BQ322,BQ173:BQ177,BQ29:BQ171	BQ3	=IF(BP3=1,CQ3,"0")
BR3:BR27,BR324:BR343,BR180:BR322,BR173:BR177,BR29:BR171	BR3	=IF(BP3=1,CR3,"0")

 

[aghaffar82]

Try this in cell BU3:
=IF(AND(BP3=1,BS3=1,BT3=1),CQ3,"")

 

[wmichael]

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]

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.

 

[wmichael]

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]

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]

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!