sumif in nxn grid (n>2)

[Briana2]

I have a list of classes, teachers, and periods. I need to sum the periods for each teacher when their name is typed in.
The problem is that the data is not arranged in a 3x2 grid.
eg
Math 8 3 Joe Math 9 4 Joe Math 10 5 Sam
Eng8 4 Lucy Eng 9 5 Lucy Eng 10 6 Joe
Sci 8 4 Bob Sci 9 4 Lucy Sci 10 6 Bob

Then each teacher's name is listed in a column. I would like the sum of their periods assigned listed to the right of the name.

Joe 13
Sam 5
Bob 10
Lucy 13


I know how to use sumif if there were only 2 columns.


Thanks

 

[Eric W]

Welcome to the Board!

Try this:

	A	B	C	D	E	F	G	H	I	J	K	L
Math	Joe	Math	Joe	Math	Sam
Eng	Lucy	Eng	Lucy	Eng	Joe
Sci	Bob	Sci	Lucy	Sci	Bob
Joe
Sam	5
Bob
Lucy	13

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]

<!--

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: rig"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=SUMPRODUCT(--($D$1:$L$3=A5),$C$1:$K$3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Briana2]

One more question,
Can I use a wildcard * with sumproduct if I have 2 or 3 teacher names in the same cell?
Thanks again

 

[Eric W]

Not actually a wildcard, but you can use SEARCH to see if a given name is in a cell:

	A	B	C	D	E	F	G	H	I	J	K	L
Math	Joe/Mary	Math	Joe	Math	Sam
Eng	Lucy	Eng	Lucy	Eng	Joe
Sci	Bob	Sci	Lucy	Sci	Bob
Joe
Sam	5
Bob
Lucy
Mary

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=SUMPRODUCT(--ISNUMBER(SEARCH(A5,$D$1:$L$3)),$C$1:$K$3)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Briana2]

Eric, you are genius!
Thanks!

 

[Eric W]

Happy to help!