How to get the sum from multiple rows when found with vlookup

[Gerald Crumbley]

We are a Training entity. I need to pull monthly reports showing Enrollments for our courses.

Issue: A course may be taught multiple times each month at 50+ locations throughout the State. The monthly data will have the same course name, but with different locations and different enrollment, passed, failed numbers

I've got VLOOKUP to give me a list, however it only returns the data from the first row it finds. Is it possible to get a sum of every row with the same course name?

 

[Joe4]

If you are trying to get a sum from multiple lines matching a certain criteria, perhaps a SUMIFS formula would be better to use in your scenario.
See: SUMIFS Function

Also, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jambi46n2]

We are a Training entity. I need to pull monthly reports showing Enrollments for our courses.

Issue: A course may be taught multiple times each month at 50+ locations throughout the State. The monthly data will have the same course name, but with different locations and different enrollment, passed, failed numbers

I've got VLOOKUP to give me a list, however it only returns the data from the first row it finds. Is it possible to get a sum of every row with the same course name?

You can create a column called "Unique ID" then combine multiple columns that are relevant to establish the unique value. Example: =A2&B2&C2&D2 each cell is a value of "Course Name", "Location", "Enrollment", "Passed", "Failed". Rather than using the "course name" which has duplicates, have your VLOOKUP reference the "Unique ID" column to pull each row. The combined values may be sufficient enough for a unique result.

 

[awoohaw]

how about:

Book4
	A	B	C	D	E
2
3	COURSE	Location	Enrolment	Pass	Fail
4	COURSE1	Location1	33	21	12
5	COURSE2	Location2	28	17	11
6	COURSE2	Location3	31	19	12
7	COURSE1	Location4	43	25	18
8	COURSE3	Location5	24	10	14
9	COURSE1	Location6	32	18	14
10	COURSE1	Location7	23	10	13
11	COURSE3	Location8	31	21	10
12	COURSE2	Location9	38	18	20
13
14
15	COURSE1	131	74	57
16	COURSE2	97	54	43
17	COURSE3	55	31	24
Sheet3

Cell Formulas
Range	Formula
B15:D17	B15	=SUM(CHOOSECOLS(FILTER($C$4:$E$12,$A$4:$A$12=$A15,""),COLUMN(B15)-1))

 

[Gerald Crumbley]

Thank you, I will play with these two suggestions. I'm not as smart as I look (although, that is an arguable point), if anyone can send me a formula that works . . . I would appreciate it.

I import raw data to a "Month" tab. Column B is course Name; Column K is Total Enrollments; there are actually 49 rows with the same course name and different enrollment numbers for each.
(mock numbers)

JanTab:

Course Name	Location	Total	Pass	Fail
Firearms Requalification including Use of Deadly Force and De-escalation	Site A	2​	2​	0​
Firearms Requalification including Use of Deadly Force and De-escalation	Site B	17​	14​	3​
Firearms Requalification including Use of Deadly Force and De-escalation	Site C	10​	10​	0​
Firearms Requalification including Use of Deadly Force and De-escalation	Site D	24​	20​	0​

On an "Enrollments" tab, all courses are listed in rows, month totals are in columns. I need to get a sum of all locations, not just the first one:

Enrollments Tab:

Course Name	January	February	March
Advanced Perimeter	0	0	0
Basic Sex Offender Management training	0	0	0
Firearms Requalification including Use of Deadly Force and De-escalation	2	0	0

Here is my formula starting a cell B18 on Enrollments Tab: =IFNA(VLOOKUP($A18,Jan!$B$1:$M$600,10,FALSE),0)

I need to get a sum of all locations, not just the first one.

 

[Gerald Crumbley]

awoohaw,

I tried the below formula but it's not working. I wanted your $C$4:$E$12 to only be column K ($K$27:$K$500). I wanted your $A$4:$A$12 to be column B and return to column N ($B$27:$B$500=$N27,""). and your COLUMN B15 to be column O (COLUMN(O27)-1))

What have I done wrong?

Yours: =SUM(CHOOSECOLS(FILTER($C$4:$E$12,$A$4:$A$12=$A15,""),COLUMN(B15)-1))
Mine: =SUM(CHOOSECOLS(FILTER($K$27:$K$500,$B$27:$B$500=$N27,""),COLUMN(O27)-1))

 

[awoohaw]

the column calculation at the end needs to result as 1 in the first column. So, O is column 15, I think,
so you should use (COLUMN(O27)-14). the cell reference should be the first cell in the first column of the calculation output.

So, I cannot be absolutely certain of where you are starting your formulas. This is why posting a mini worksheet using the xl2bb is so helpful. I could have built the formula for you in the exact cell to start in. Link to xl2bb is below.

 

[Gerald Crumbley]

Sorry, apparently the State has blocked me adding xl2bb.

I can't get it to work . . .

=SUM(CHOOSECOLS(FILTER($K$27:$K$500,$B$27:$B$500=$N27,""),COLUMN(O27)-14))

 

[awoohaw]

ok. the first range in the filter function should be 3 columns wide. so try...

=SUM(CHOOSECOLS(FILTER($K$27:$M$500,$B$27:$B$500=$N27,""),COLUMN(O27)-14))

 

[Gerald Crumbley]

My co-worker came up with a formula that works. The Month changes for each column. (also one for the Passed tab):

=IFNA(SUMPRODUCT(SUMIFS(Jan!$K:$K,Jan!$B:$B,Enrollments!$A2)),0)