Search for a specific word in Range

[roxdrob]

Hi,

I am in need of assistance with a formula that searches for a specific word or words (a name) in a range that is multiple columns and rows. How would I go about doing that? I'm sure it's an easy answer, I'm just a bit rusty. I would like the formula to return the sum of instances. I hope this makes sense.

Joseph White 137
Sally Dixon 432


TIA

 

[jtakw]

Hi,

Seems you're not showing your whole table/calendar, and your picture doesn't show Any Row/Column references.
Please consider posting in XL2BB, see my signature, or at least in Table format.
May be this, change/adjust cell/range references accordingly:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1																						Joseph White	137
2																						Sally Dixon	432
3
4
5
6
7							100	Joseph White
8
9
10
11
12													37	Joseph White
13
14
15
16
17													432	Sally Dixon
18
19
20
21
22
23
24
25
Sheet1008

Cell Formulas
Range	Formula
W1:W2	W1	=SUMIF(B$1:T$25,V1,A$1:S$25)

 

[roxdrob]

Calendar Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	January 2022
2
3
4
5
6
7
8
9	Sunday		Monday			Tuesday			Wednesday			Thursday			Friday			Saturday
10			Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö			-		Sum
11	26		27			28			29			30			31			1			Adam Hull
12																					Cameron Roth
13																					Joe Cool
14																					Kelsey Lou
15																					Marshall Ditz
16																					Meredith Gray
17	2		3			4			5			6			7			8			Michelle Harris
18			129.98	Joe Cool					42.00	Michelle Harris		137.50	Sid Peavy								Patrick Adams
19									62.50	Cameron Roth											Sid Peavy
20
21
22
23	9		10			11			12			13			14			15
24									69.00	Patrick Adams					100.00	Marshall Ditz
25
26
27
28
29	16		17			18			19			20			21			22
30															72.75	Meredith Gray
31
32
33
34
35	23		24			25			26			27			28			29
36									44.00	Adam Hull
37
38
39
40
41	30		31			Notes
42
43
44			55.55	Kelsey Lou
45															https://www.vertex42.com/calendars/...ium=file&utm_campaign=office&utm_content=text	https://www.vertex42.com/calendars/...ium=file&utm_campaign=office&utm_content=text	https://www.vertex42.com/calendars/...ium=file&utm_campaign=office&utm_content=text	https://www.vertex42.com/calendars/...ium=file&utm_campaign=office&utm_content=text	https://www.vertex42.com/calendars/...ium=file&utm_campaign=office&utm_content=text
46															https://www.vertex42.com/calendars/...dium=file&utm_campaign=office&utm_content=url	https://www.vertex42.com/calendars/...dium=file&utm_campaign=office&utm_content=url	https://www.vertex42.com/calendars/...dium=file&utm_campaign=office&utm_content=url	https://www.vertex42.com/calendars/...dium=file&utm_campaign=office&utm_content=url	https://www.vertex42.com/calendars/...dium=file&utm_campaign=office&utm_content=url
Jan

Cell Formulas
Range	Formula
A1	A1	=DATE('[2022BillsCalendar(New).xlsx]DateEntry'!C18,'[2022BillsCalendar(New).xlsx]DateEntry'!C20,1)
A9,C9,F9,I9,L9,O9,R9	A9	=A11
T10	T10	=COUNTIF(C12:Q14,"x")+COUNTIF(C18:Q20,"x")+COUNTIF(C24:Q26,"x")+COUNTIF(C30:Q32,"x")+COUNTIF(C36:Q38,"x")+COUNTIF(C42:E44,"x")
A11	A11	=$A$1-(WEEKDAY($A$1,1)-('[2022BillsCalendar(New).xlsx]DateEntry'!start_day-1))-IF((WEEKDAY($A$1,1)-('[2022BillsCalendar(New).xlsx]DateEntry'!start_day-1))<=0,7,0)+1
C11,C41,C35,C29,C23,C17	C11	=A11+1
F11,R35,O35,L35,I35,F35,R29,O29,L29,I29,F29,R23,O23,L23,I23,F23,R17,O17,L17,I17,F17,R11,O11,L11,I11	F11	=C11+1
A17,A41,A35,A29,A23	A17	=R11+1

Named Ranges
Name	Refers To	Cells
Print_Area	=Jan!$A$1:$S$46	A11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P24	Expression	=Q24="x"	text	NO
O24:P24	Expression	=Q24="x"	text	NO
J19	Expression	=K19="x"	text	NO
I19:J19	Expression	=K19="x"	text	NO
J24	Expression	=K24="x"	text	NO
I24:J24	Expression	=K24="x"	text	NO
E42:E44	Expression	=E42="x"	text	NO
D42:D44	Expression	=E42="x"	text	NO
C42:E44	Expression	=E42="x"	text	NO
P36	Expression	=Q36="x"	text	NO
O36:P36	Expression	=Q36="x"	text	NO
M36	Expression	=N36="x"	text	NO
L36:M36	Expression	=N36="x"	text	NO
H36:H38,K36:K38,N36:N38,Q36:Q38	Expression	=H36="x"	text	NO
G36:G38,J36:J38,M37:M38,P37:P38	Expression	=H36="x"	text	NO
F37:Q38,F36:K36,N36,Q36	Expression	=H36="x"	text	NO
E36:E38	Expression	=E36="x"	text	NO
D36:D38	Expression	=E36="x"	text	NO
C36:E38	Expression	=E36="x"	text	NO
P30	Expression	=Q30="x"	text	NO
O30:P30	Expression	=Q30="x"	text	NO
M30	Expression	=N30="x"	text	NO
L30:M30	Expression	=N30="x"	text	NO
H30:H32,K30:K32,N30:N32,Q30:Q32	Expression	=H30="x"	text	NO
G30:G32,J30:J32,M31:M32,P31:P32	Expression	=H30="x"	text	NO
F31:Q32,F30:K30,N30,Q30	Expression	=H30="x"	text	NO
E30:E32	Expression	=E30="x"	text	NO
D30:D32	Expression	=E30="x"	text	NO
C30:E32	Expression	=E30="x"	text	NO
M24	Expression	=N24="x"	text	NO
L24:M24	Expression	=N24="x"	text	NO
H24:H26,K24:K26,N24:N26,Q24:Q26	Expression	=H24="x"	text	NO
G24:G26,J25:J26,M25:M26,P25:P26	Expression	=H24="x"	text	NO
F25:Q26,F24:H24,N24,Q24,K24	Expression	=H24="x"	text	NO
E24:E26	Expression	=E24="x"	text	NO
D24:D26	Expression	=E24="x"	text	NO
C24:E26	Expression	=E24="x"	text	NO
P18	Expression	=Q18="x"	text	NO
O18:P18	Expression	=Q18="x"	text	NO
M18	Expression	=N18="x"	text	NO
L18:M18	Expression	=N18="x"	text	NO
H18:H20,K18:K20,N18:N20,Q18:Q20	Expression	=H18="x"	text	NO
G18:G20,J18,M19:M20,P19:P20,J20	Expression	=H18="x"	text	NO
F20:Q20,F18:K18,N18,Q18,F19:H19,K19:Q19	Expression	=H18="x"	text	NO
E18:E20	Expression	=E18="x"	text	NO
D18:D20	Expression	=E18="x"	text	NO
C18:E20	Expression	=E18="x"	text	NO
P12	Expression	=Q12="x"	text	NO
O12:P12	Expression	=Q12="x"	text	NO
M12	Expression	=N12="x"	text	NO
L12:M12	Expression	=N12="x"	text	NO
H12:H14,K12:K14,N12:N14,Q12:Q14	Expression	=H12="x"	text	NO
G12:G14,J12:J14,M13:M14,P13:P14	Expression	=H12="x"	text	NO
F13:Q14,F12:K12,N12,Q12	Expression	=H12="x"	text	NO
E12:E14	Expression	=E12="x"	text	NO
D12:D14	Expression	=E12="x"	text	NO
C12:E14	Expression	=E12="x"	text	NO
O35	Expression	=MONTH(O35)<>MONTH($A$1)	text	NO
O35	Expression	=OR(WEEKDAY(O35,1)=1,WEEKDAY(O35,1)=7)	text	NO
O29	Expression	=MONTH(O29)<>MONTH($A$1)	text	NO
O29	Expression	=OR(WEEKDAY(O29,1)=1,WEEKDAY(O29,1)=7)	text	NO
O23	Expression	=MONTH(O23)<>MONTH($A$1)	text	NO
O23	Expression	=OR(WEEKDAY(O23,1)=1,WEEKDAY(O23,1)=7)	text	NO
O17	Expression	=MONTH(O17)<>MONTH($A$1)	text	NO
O17	Expression	=OR(WEEKDAY(O17,1)=1,WEEKDAY(O17,1)=7)	text	NO
O11	Expression	=MONTH(O11)<>MONTH($A$1)	text	NO
O11	Expression	=OR(WEEKDAY(O11,1)=1,WEEKDAY(O11,1)=7)	text	NO
L11	Expression	=MONTH(L11)<>MONTH($A$1)	text	NO
L11	Expression	=OR(WEEKDAY(L11,1)=1,WEEKDAY(L11,1)=7)	text	NO
L17	Expression	=MONTH(L17)<>MONTH($A$1)	text	NO
L17	Expression	=OR(WEEKDAY(L17,1)=1,WEEKDAY(L17,1)=7)	text	NO
L23	Expression	=MONTH(L23)<>MONTH($A$1)	text	NO
L23	Expression	=OR(WEEKDAY(L23,1)=1,WEEKDAY(L23,1)=7)	text	NO
L29	Expression	=MONTH(L29)<>MONTH($A$1)	text	NO
L29	Expression	=OR(WEEKDAY(L29,1)=1,WEEKDAY(L29,1)=7)	text	NO
L35	Expression	=MONTH(L35)<>MONTH($A$1)	text	NO
L35	Expression	=OR(WEEKDAY(L35,1)=1,WEEKDAY(L35,1)=7)	text	NO
I35	Expression	=MONTH(I35)<>MONTH($A$1)	text	NO
I35	Expression	=OR(WEEKDAY(I35,1)=1,WEEKDAY(I35,1)=7)	text	NO
I29	Expression	=MONTH(I29)<>MONTH($A$1)	text	NO
I29	Expression	=OR(WEEKDAY(I29,1)=1,WEEKDAY(I29,1)=7)	text	NO
I23	Expression	=MONTH(I23)<>MONTH($A$1)	text	NO
I23	Expression	=OR(WEEKDAY(I23,1)=1,WEEKDAY(I23,1)=7)	text	NO
I17	Expression	=MONTH(I17)<>MONTH($A$1)	text	NO
I17	Expression	=OR(WEEKDAY(I17,1)=1,WEEKDAY(I17,1)=7)	text	NO
I11	Expression	=MONTH(I11)<>MONTH($A$1)	text	NO
I11	Expression	=OR(WEEKDAY(I11,1)=1,WEEKDAY(I11,1)=7)	text	NO
F35	Expression	=MONTH(F35)<>MONTH($A$1)	text	NO
F35	Expression	=OR(WEEKDAY(F35,1)=1,WEEKDAY(F35,1)=7)	text	NO
F29	Expression	=MONTH(F29)<>MONTH($A$1)	text	NO
F29	Expression	=OR(WEEKDAY(F29,1)=1,WEEKDAY(F29,1)=7)	text	NO
F23	Expression	=MONTH(F23)<>MONTH($A$1)	text	NO
F23	Expression	=OR(WEEKDAY(F23,1)=1,WEEKDAY(F23,1)=7)	text	NO
F17	Expression	=MONTH(F17)<>MONTH($A$1)	text	NO
F17	Expression	=OR(WEEKDAY(F17,1)=1,WEEKDAY(F17,1)=7)	text	NO
F11	Expression	=MONTH(F11)<>MONTH($A$1)	text	NO
F11	Expression	=OR(WEEKDAY(F11,1)=1,WEEKDAY(F11,1)=7)	text	NO
C41	Expression	=MONTH(C41)<>MONTH($A$1)	text	NO
C41	Expression	=OR(WEEKDAY(C41,1)=1,WEEKDAY(C41,1)=7)	text	NO
C35	Expression	=MONTH(C35)<>MONTH($A$1)	text	NO
C35	Expression	=OR(WEEKDAY(C35,1)=1,WEEKDAY(C35,1)=7)	text	NO
C29	Expression	=MONTH(C29)<>MONTH($A$1)	text	NO
C29	Expression	=OR(WEEKDAY(C29,1)=1,WEEKDAY(C29,1)=7)	text	NO
C23	Expression	=MONTH(C23)<>MONTH($A$1)	text	NO
C23	Expression	=OR(WEEKDAY(C23,1)=1,WEEKDAY(C23,1)=7)	text	NO
C17	Expression	=MONTH(C17)<>MONTH($A$1)	text	NO
C17	Expression	=OR(WEEKDAY(C17,1)=1,WEEKDAY(C17,1)=7)	text	NO
R11,R17,R23,R29,R35	Expression	=MONTH(R11)<>MONTH($A$1)	text	NO
R11,R17,R23,R29,R35	Expression	=OR(WEEKDAY(R11,1)=1,WEEKDAY(R11,1)=7)	text	NO
A11,C11,A17,A23,A29,A35,A41	Expression	=MONTH(A11)<>MONTH($A$1)	text	NO
A11,C11,A17,A23,A29,A35,A41	Expression	=OR(WEEKDAY(A11,1)=1,WEEKDAY(A11,1)=7)	text	NO

 

[roxdrob]

Did I do that correctly?

 

[jtakw]

Did I do that correctly?

Yes, thanks for posting with XL2BB.

Add a column between Sunday and Monday, so that it's consistent with the rest of your calendar, All days have 3 columns, whereas Sunday only has 2.

This formula in W11 copied down:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
9	Sunday			Monday			Tuesday			Wednesday			Thursday			Friday			Saturday
10				Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö					Sum
11	26			27			28			29			30			31			1			Adam Hull	44
12																						Cameron Roth	62.5
13																						Joe Cool	129.98
14																						Kelsey Lou	55.55
15																						Marshall Ditz	100
16																						Meredith Gray	72.75
17	2			3			4			5			6			7			8			Michelle Harris	42
18				129.98	Joe Cool					42	Michelle Harris		137.5	Sid Peavy								Patrick Adams	69
19										62.5	Cameron Roth											Sid Peavy	137.5
20
21
22
23	9			10			11			12			13			14			15
24										69	Patrick Adams					100	Marshall Ditz
25
26
27
28
29	16			17			18			19			20			21			22
30																72.75	Meredith Gray
31
32
33
34
35	23			24			25			26			27			28			29
36										44	Adam Hull
37
38
39
40
41	30			31			Notes
42
43
44				55.55	Kelsey Lou
45
46
Sheet1011

Cell Formulas
Range	Formula
W11:W19	W11	=SUMIF(B$11:T$46,V11,A$11:S$46)

 

[roxdrob]

I only use the days Monday through Friday. Saturday and Sunday won't be used.

 

[jtakw]

I only use the days Monday through Friday. Saturday and Sunday won't be used.

Then no need to add a Sunday column if you don't want to, and just change the range in the formula, now in V11 copied down:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
9	Sunday		Monday			Tuesday			Wednesday			Thursday			Friday			Saturday
10			Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö	Amt	Description	Ö					Sum
11	26		27			28			29			30			31			1			Adam Hull	44
12																					Cameron Roth	62.5
13																					Joe Cool	129.98
14																					Kelsey Lou	55.55
15																					Marshall Ditz	100
16																					Meredith Gray	72.75
17	2		3			4			5			6			7			8			Michelle Harris	42
18			129.98	Joe Cool					42	Michelle Harris		137.5	Sid Peavy								Patrick Adams	69
19									62.5	Cameron Roth											Sid Peavy	137.5
20
21
22
23	9		10			11			12			13			14			15
24									69	Patrick Adams					100	Marshall Ditz
25
26
27
28
29	16		17			18			19			20			21			22
30															72.75	Meredith Gray
31
32
33
34
35	23		24			25			26			27			28			29
36									44	Adam Hull
37
38
39
40
41	30		31			Notes
42
43
44			55.55	Kelsey Lou
45
46
Sheet1011

Cell Formulas
Range	Formula
V11:V19	V11	=SUMIF(D$11:P$46,U11,C$11:O$46)

 

[roxdrob]

Great! Thank you so much!

 

[jtakw]

You're welcome, thanks for the feedback.