Formula that "searches" for a column by title

[Lea]

Hello everyone,

I have a spreadsheet with a column that I need to total. The stinker here is that the column will move to the right as we add data to it. I am hoping to have a formula that looks for all instances of a person's name in column B and when it finds that name then find the column titled "Number of Tests". Then add the rows that contain that person's name.

This might make it simpler to understand what I need...
Here is my formula for a fixed column.
=SUMIF('Data Dump'!B:B, "*Philip*", 'Data Dump'!E:E)
But instead of E:E, I need it to search for the column labeled "Number of Tests". If it helps, "Number of Tests" will always be in row 6.

I don't even know if this is possible. I appreciate any help you can give.

Thank, you,
Lea

 

[kweaver]

This might work:

=SUMPRODUCT(("Lea"=A7:A14)*(INDEX(A7:G14,,MATCH("Number of Tests",A6:Z6,0))))

 

[bebo021999]

One way:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
3	Sum
4	6
5
6								Number of Tests
7
8
9	asas							1
10	sad Philip asdasd							2
11	asdasd							3
12	Philip							4
13
14
15
16
17
Sheet1

Cell Formulas
Range	Formula
B4	B4	=SUMPRODUCT(ISNUMBER(SEARCH("Philip",$B$7:$B$17))*($C$6:$M$6="Number of Tests")*$C$7:$M$17)

 

[AhoyNC]

Couple of other options. FILTER option will only work in Excel 365
Don't use whole column references or you will get an error.
In these examples the data starts in row 7 and I have it going to row 10000 and I have the columns going to column AZ.

Book4
	A	B	C	D	E	F	G
1
2
3		Philip	38
4			38
5
6		Name		Num Test2	Num Test3	Number of Tests
7		Ted		1	5	12
8		Philip		3	6	25
9		Sam		5	8	1
10		Philip		6	9	3
11		Sally		5	74	6
12		Philip		4	21	6
13		Philip		5	25	4
14
Sheet2

Cell Formulas
Range	Formula
C3	C3	=SUMIF($B$7:$B$10000,$B$3,INDEX($C$7:$Z$10000,0,MATCH("Number of Tests",$C$6:$AZ$6,0)))
C4	C4	=SUM(FILTER(FILTER($D$7:$AZ10000,$D$6:$AZ$6="Number of Tests"),$B$7:$B$10000=$B$3))

 

[Lea]

Hello everyone,

Thank you so much for your answers. I am having trouble adapting all of the formulas to my spreadsheet. When I add the reference to the Data Dump sheet things go haywire. And I realized that my trying to reference the column I need titled Number of Tests won't work as the raw data has all of the columns titled the same. I have uploaded and image of the sheet named Data Dump that the formula will be referencing. The formula itself will be on a sheet named 2023.

In April, I need the totals that are in Column I. Next month we will run a report and paste it into Data Dump and column I will become the May totals and column J will be the total of all the months. So I don't know how to get YTD totals when the YTD column will move over to the right each month.

Here are just two of the formulas that I tried to adapt.

=SUM(FILTER(FILTER('Data Dump'!$E$9:$AZ10000,'Data Dump'!$E$6:$AZ$6="Number of Tests"), 'Data Dump'!$B$9:$B$10000=$A$3))

=SUMIF('Data Dump'!$B$9:$B$10000,$A$2,INDEX('Data Dump'$E$9:$Z$10000,0,MATCH("Number of Tests",'Data Dump!'$D$7:'Data Dump'$A$Z9,0)))

I am sorry this is turning into a complicated mess.

Lea

 

[kweaver]

I think you need to (a) use XL2BB to show your data and (b) explain and show where the totals in columns E to H are coming from.

 

[Lea]

I think you need to (a) use XL2BB to show your data and (b) explain and show where the totals in columns E to H are coming from.

Hi kweaver,

Every month we run a report that pulls our testing information. We then export that data to excel. The screenshots below are what the data looks like after it is exported. As you can see the YTD total in March was in column H and in April, the YTD total moved to column I. The data is broken into sections of teams- see screenshot #3- and the same person can be on more than one team. That is why I need the formula to look at column B to identify the worker and then count the total number of tests that each person has run YTD. I don't know how to count the total number of tests since the total column will move as the year progresses. One thought I had was the range E8:P8 would cover Jan through Dec. So, I thought I could write a formula to recognize if there is a value in that range to count that column. I either did it wrong or there may be an issue because YTD total cells are merged. And I have no idea how the formula sees (screenshot #1) H8 when it is merged with H6 and H7.

My apologies, but I am on a work computer, and I do not have administrator rights. I cannot download XL2BB.

Lea



Screenshot #1

Screenshot #2

Screenshot #3

 

[kweaver]

RE: And I have no idea how the formula sees (screenshot #1) H8 when it is merged with H6 and H7.
When those cells are merged, the text is in H6. That's a potential issue when the other previous columns have the same text.

However, below are 2 illustrations that, when adjusted for one set of expanding data, should work.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M
6				Number of Tests	Number of Tests	Number of Tests	Total Number of Tests
7				2023	2023	2023
8				Jan	Feb	Mar
9	Cody, Clark			27	49	6	82					Total YTD
10	Jones, Lea			44	95	10	149				Cody, Clark	153
11	Stevens, Don				1		1				Jones, Lea	209
12	Cody, Clark			22	42	7	71				Stevens, Don	1
13	Jones, Lea			10	20	30	60
14
15
16
17				Number of Tests	Number of Tests	Number of Tests	Number of Tests	Total Number of Tests
18				2023	2023	2023	2023
19				Jan	Feb	Mar	Apr
20	Cody, Clark			27	49	6	5	87				Total YTD
21	Jones, Lea			44	95	10	6	155			Cody, Clark	161
22	Stevens, Don				1			1			Jones, Lea	255
23	Cody, Clark			22	42	7	3	74			Stevens, Don	1
24	Jones, Lea			10	20	30	40	100
Sheet2

Cell Formulas
Range	Formula
M10:M12	M10	=SUMPRODUCT((L10=$B$9:$B$13)*INDEX(E$9:K$13,,MATCH("Total Number of Tests",$E$6:$K$6,0)))
H9:H13	H9	=SUM(E9:G9)
M21:M23	M21	=SUMPRODUCT((L21=$B$20:$B$24)*INDEX(E$20:K$24,,MATCH("Total Number of Tests",$E$17:$K$17,0)))
I20:I24	I20	=SUM(E20:H20)

 

[kweaver]

Alternatively, you could do this and adjust the bottom summary according to the number of employees there are:

Book1
	B	C	D	E	F	G	H
6				Number of Tests	Number of Tests	Number of Tests	Total Number of Tests
7				2023	2023	2023
8				Jan	Feb	Mar
9	Cody, Clark			27	49	6	82
10	Jones, Lea			44	95	10	149
11	Stevens, Don				1		1
12	Cody, Clark			22	42	7	71
13	Jones, Lea			10	20	30	60
14
15
16
17
18		Total YTD
19	Cody, Clark	153
20	Jones, Lea	209
21	Stevens, Don	1
Sheet2 (2)

Cell Formulas
Range	Formula
H9:H13	H9	=SUM(E9:G9)
B19:B21	B19	=UNIQUE(B9:B13)
C19:C21	C19	=SUMPRODUCT((B19=$B$9:$B$13)*INDEX(E$9:Q$13,,COUNTA($E$6:$Q$6)))
Dynamic array formulas.