SUMIFS/Vlookup using two worksheets?

K

kznmrexcel

Board Regular
Joined
Jun 16, 2010
Messages
86
Office Version
  1. 2016
Platform
  1. MacOS
Hi,
I am trying to find a formula that will look up numbers of one worksheet and give me totals on another worksheet. The specifics:
Column A Column B Column C
Room # Class period The enrollment count

The catch is that some classrooms and periods are listed twice, as two different sections of students are combined into a single room during the same period. I need to know the total count of student bodies ("Warm body count") for each classroom for each period. Example from the worksheet called classLoad:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Classroom[/TD]
[TD]Period[/TD]
[TD]Total enrolled[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]1[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

On a separate sheet named perTotals, I have a grid and want a total of the warm body count for each classroom during each class period.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Classroom[/TD]
[TD]Per1[/TD]
[TD]Per2[/TD]
[TD]Per3[/TD]
[TD]Per4[/TD]
[TD]Per5[/TD]
[TD]Per6[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Etc.

I need a formula that will count all the entries for room A1 (column A), per. 1 (column B) found in column C, Total enrolled, from the worksheet called classLoad to return the sum in B2 in worksheet perTotals.

Any help is much appreciated.
Thanks,
Karen
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes

Book1
ABC
1ClassroomPeriodTotal enrolled
2A1135
3A113
4A2221
5A3118
6A3112
classLoad



Book1
ABCDEFG
1ClassroomPer1Per2Per3Per4Per5Per6
2A13800000
3A20210000
4A33000000
5
perTotals


In A3 of perTotals, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(classLoad!$A$2:$A$6,SMALL(IF(FREQUENCY(IF(classLoad!$A$2:$A$6<>"",MATCH(classLoad!$A$2:$A$6,classLoad!$A$2:$A$6,0)),ROW(classLoad!$A$2:$C$6)-ROW(INDEX(classLoad!$A$2:$C$6,1,1))+1),ROW(classLoad!$A$2:$C$6)-ROW(INDEX(classLoad!$A$2:$C$6,1,1))+1),ROWS($1:1))),"")

In B2 just enter, copy across, and down:

=IF($A2="","",SUMIFS(classLoad!$C$2:$C$6,classLoad!$A$2:$A$6,$A2,classLoad!$B$2:$B$6,SUBSTITUTE(LOWER(B$1),"per","")))
 
Upvote 0
Hi, Aladin,

Thanks for your reply. I tried using your formulas but couldn't get them to return the values from the other worksheet. I'm on a Mac using Excel 2011 if that helps.

The classLoad worksheet has classrooms listed manually in Column A. The perTotals worksheet also has the classrooms listed manually, so I don't know about entering a formula into the cells in column A.
Right now, the sheet where I want to do the calculations looks like this:


ClassroomPer1Per2Per3Per4Per5Per6
A1
A2
A3
A4
A5
B2
B3
B4
B5
B6
C1

<tbody>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]

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

[TD="align: center"]2[/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"]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: 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: center"]5[/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: center"]7[/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: center"]9[/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"]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: center"]11[/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"]12[/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>
perTotals​

...Etc. all the way up to G3. I need to get a formula to enter in columns B, C, D, E, F, and G for each classroom listed that will count up how many students are listed for each entry that matches the classroom number and the class period, periods 1 - 6. Is there a way to do this?

Thanks for your help with this.
Best,
Karen
 
Last edited:
Upvote 0
Hi, Aladin,

Thanks for your reply. I tried using your formulas but couldn't get them to return the values from the other worksheet. I'm on a Mac using Excel 2011 if that helps.

The classLoad worksheet has classrooms listed manually in Column A. The perTotals worksheet also has the classrooms listed manually, so I don't know about entering a formula into the cells in column A.
Right now, the sheet where I want to do the calculations looks like this:


[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Classroom[/TD]
[TD]Per1[/TD]
[TD]Per2[/TD]
[TD]Per3[/TD]
[TD]Per4[/TD]
[TD]Per5[/TD]
[TD]Per6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]A1[/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]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]A2[/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]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]A3[/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]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]A4[/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]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]A5[/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]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]B2[/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]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]B3[/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]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]B4[/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]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]B5[/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]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]B6[/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]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]C1[/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]
[/TR]
</tbody>[/TABLE]
perTotals​

...Etc. all the way up to G3. I need to get a formula to enter in columns B, C, D, E, F, and G for each classroom listed that will count up how many students are listed for each entry that matches the classroom number and the class period, periods 1 - 6. Is there a way to do this?

Thanks for your help with this.
Best,
Karen
 
Upvote 0
In B2 of the perTotals sheet enter the following formula, copy across to G2, then down:

=IF($A2="","",SUMIFS(classLoad!$C$2:$C$60,classLoad!$A$2:$A$60,$A2,classLoad!$B$2:$B$60,SUBSTITUTE(LOWER(B$1),"per","")))
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Complex subtotal count and multiply
Replies
2
Views
547
Lightkeepr
L
K
  • Question Question
Convert VLOOKUP Cell Value to a Value on a Separate Worksheet
Replies
9
Views
907
Fluff
Fluff
N
  • Question Question
Excel Novice: Would like to define CountIFS Critera as a variable array.
Replies
5
Views
579
Eric W
Eric W
B
  • Question Question
Formula Help
Replies
9
Views
787
oldbrewer
O
W
  • Question Question
Conditional formation/ Highlight a row based on specific columns
Replies
0
Views
596
wngadea
W

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top