adding a sum of one column if it meets a criteria and show in another "master sheet"

K

kash79

New Member
Joined
Feb 15, 2013
Messages
5
I am trying to calculate a stat for i school i work at, i have one spreadsheet that has yearly stats on things . so i have one column with name and then one with date then stat, then another stat. so i want to add their stats for a ongoing year and and have it in a master spreadsheet. for example sam jones , date, stat, stat, so i want to add the first stat of every sam jones entry for the current year and have it shown in the master spreadsheet for a cross year spreadsheet. i have tried if, sumif, and other various fx's but they always say error #name? or other random errors, what is the right function????? Please help i need to get the bugs worked out soon for our entire staff to use for iep's and other analysis!

Thank you
 

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
Hi and welcome to MrExcel.

Some sample data and your expected results may help you to get the answer you seek.
In the mean time, here is my sample data and example results based on what I think you are asking.....

Sample data....

Excel Workbook
ABCDE
1NameDateStat1Stat2
2Bill01/01/2012100102
3Bob02/01/2012200202
4Ben03/01/2012300302
5Don04/01/2012400402
6Dan05/01/2012500502
7Bill01/01/2013600602
8Bob02/01/2013700702
9Ben03/01/2013800802
10Don04/01/2013900902
11Bill05/01/201310001002
12Bob06/01/201311001102
13Ben07/01/201312001202
14
Sheet1


Example results1.....

Excel Workbook
ABCDE
1NameStat1Year
2Bill16002013
3Bob1800
4Ben2000
5Don900
6Dan0
7
Master


Example results2.....

Excel Workbook
ABCDE
1NameStat1Year
2Bill1002012
3Bob200
4Ben300
5Don400
6Dan500
7
Master


I hope that helps.

Good luck.

Ak
 
Upvote 0
Akashwani said:
Hi and welcome to MrExcel.

Some sample data and your expected results may help you to get the answer you seek.
In the mean time, here is my sample data and example results based on what I think you are asking.....

Sample data....

Sheet1

*ABCDE
*
Bill*
Bob*
Ben*
Don*
Dan*
Bill*
Bob*
Ben*
Don*
Bill*
Bob*
Ben*
*****

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 75px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 20px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Stat1[/TD]
[TD="align: center"]Stat2[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]01/01/2012[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]102[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]02/01/2012[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]202[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]03/01/2012[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]302[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]04/01/2012[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]402[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]05/01/2012[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]502[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]01/01/2013[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]602[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"]02/01/2013[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]702[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]03/01/2013[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]802[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: right"]04/01/2013[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]902[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: right"]05/01/2013[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1002[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: right"]06/01/2013[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]1102[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1202[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Example results1.....

Master

*ABCDE
**
Bill**
Bob***
Ben***
Don***
Dan***
*****

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 20px;"><col style="width: 64px;"><col style="width: 20px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Stat1[/TD]

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

[TD="bgcolor: #CACACA, align: center"]2[/TD]

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

[TD="bgcolor: #FFFF00, align: right"]2013[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

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

[TD="bgcolor: #CACACA, align: center"]4[/TD]

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

[TD="bgcolor: #CACACA, align: center"]5[/TD]

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

[TD="bgcolor: #CACACA, align: center"]6[/TD]

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

[TD="bgcolor: #CACACA, align: center"]7[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=SUMPRODUCT(--(YEAR(Sheet1!$B$2:$B$13)=$D$2)*(Sheet1!$A$2:$A$13=A2),Sheet1!$C$2:$C$13)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

Example results2.....

Master

*ABCDE
**
Bill**
Bob***
Ben***
Don***
Dan***
*****

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 20px;"><col style="width: 64px;"><col style="width: 20px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Stat1[/TD]

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

[TD="bgcolor: #CACACA, align: center"]2[/TD]

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

[TD="bgcolor: #FFFF00, align: right"]2012[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

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

[TD="bgcolor: #CACACA, align: center"]4[/TD]

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

[TD="bgcolor: #CACACA, align: center"]5[/TD]

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

[TD="bgcolor: #CACACA, align: center"]6[/TD]

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

[TD="bgcolor: #CACACA, align: center"]7[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
B2=SUMPRODUCT(--(YEAR(Sheet1!$B$2:$B$13)=$D$2)*(Sheet1!$A$2:$A$13=A2),Sheet1!$C$2:$C$13)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

I hope that helps.

Good luck.

Ak
Click to expand...

This is right on track for the data i am working with, you nailed it. But how did you all of the students stats into another sheet thats that problem i am having. so for example how would you add all of bill's stats and show in another sheet in the same file? thats what i am trying to do. Thanks for your help1
*ABCDE
*
Bill*
Bob*
Ben*
Don*
Dan*
Bill*
Bob*
Ben*
Don*
Bill*
Bob*
Ben*
*****

<colgroup><col bold;?="" 30px;=""><col style="width: 75px;"><col style="width: 75px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 20px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Stat1[/TD]
[TD="align: center"]Stat2[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]

[TD="align: right"]01/01/2012[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]102[/TD]

[TD="bgcolor: #CACACA, align: center"]3[/TD]

[TD="align: right"]02/01/2012[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]202[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]

[TD="align: right"]03/01/2012[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]302[/TD]

[TD="bgcolor: #CACACA, align: center"]5[/TD]

[TD="align: right"]04/01/2012[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]402[/TD]

[TD="bgcolor: #CACACA, align: center"]6[/TD]

[TD="align: right"]05/01/2012[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]502[/TD]

[TD="bgcolor: #CACACA, align: center"]7[/TD]

[TD="align: right"]01/01/2013[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]602[/TD]

[TD="bgcolor: #CACACA, align: center"]8[/TD]

[TD="align: right"]02/01/2013[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]702[/TD]

[TD="bgcolor: #CACACA, align: center"]9[/TD]

[TD="align: right"]03/01/2013[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]802[/TD]

[TD="bgcolor: #CACACA, align: center"]10[/TD]

[TD="align: right"]04/01/2013[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]902[/TD]

[TD="bgcolor: #CACACA, align: center"]11[/TD]

[TD="align: right"]05/01/2013[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]1002[/TD]

[TD="bgcolor: #CACACA, align: center"]12[/TD]

[TD="align: right"]06/01/2013[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]1102[/TD]

[TD="bgcolor: #CACACA, align: center"]13[/TD]

[TD="align: right"]07/01/2013[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1202[/TD]

[TD="bgcolor: #CACACA, align: center"]14[/TD]

[TD="bgcolor: #CACACA, align: center"][/TD]

</tbody>
 
Upvote 0
Hi,

Sorry, I'm not sure I follow what you mean!

Assuming you have the student names etc on sheet1, HIGHLIGHT all the names and the heading.
Click the Data tab, click Advanced (Filter).
Click Copy to another location.
Click Copy to and click in any empty cell.
Click Unique records only.

You now have a list of all the student names.
Copy this list and paste it into the other sheet where you want to display the results.
Assuming you have added this list to cell A1 (down) on your other sheet and you have added the year (eg 2013) to another cell (D2), use this formula in B2 and copy down until the last name in your list....

=SUMPRODUCT(--(YEAR(Sheet1!$B$2:$B$13)=$D$2)*(Sheet1!$A$2:$A$13=A2),Sheet1!$C$2:$C$13)

You will have to change the Sheet name and cell ranges to suit your layout.

I hope that helps.

Ak
 
Upvote 0
I have it Formated properly. i can sort but my issue is when i want to add all the stats connected to a specifc student. for example for ben in your eariler sample. if he had 4 rows of data it would be easy to just do a sum. but if he has 30 + entries and it continues to grow. If there a fx that says if ben add up stat 1 for all entries with the the NAME of ben. so that if i enter data and it meets that criteria it keeps adding. does that help you understand what i am trying to do.
 
Upvote 0
Hi,

I'm sorry, that doesn't help.
If you are saying that your range is Dynamic, eg it will/could expand (grow), then I suggest that you use a range in the formula like A2:A2000 or A2:A10000.

Basically, use a range that you know that you will NEVER go beyond.

=SUMPRODUCT(--(YEAR(Sheet1!$B$2:$B$10000)=$D$2)*(Sheet1!$A$2:$A$10000=A2),Sheet1!$C$2:$C$10000)

Ak
 
Upvote 0
i still dont think you understand me. For the table below how would i add all of Ben's stat1 and place that in another sheet. As below the is two sheets the first is a yearly sheet then second sheet is the data entry sheet for the current year. So i do i sum up all of the ben's stats on in a entire much longer and more data spreadsheet with a function not manual adding of each becuase data gets entered all the time. Does this help ypu understand? thanks [TABLE="width: 500"]
<tbody>[TR]
[TD]Student[/TD]
[TD]2012-2013[/TD]
[/TR]
[TR]
[TD]Ben[/TD]
[TD]Sum from sheet below of Ben's stat 1[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Sum from sheet below of Steve's stat 1[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD]Sum from sheet below of Dave's stat 1[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Student[/TD]
[TD]date[/TD]
[TD]stat1[/TD]
[TD]stat2[/TD]
[/TR]
[TR]
[TD]ben[/TD]
[TD]1/23/2013[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD]1/23/2013[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ben[/TD]
[TD]1/23/2013[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]dave[/TD]
[TD]1/23/2013[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]bill[/TD]
[TD]1/23/2013[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ben[/TD]
[TD]1/23/2013[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]ben[/TD]
[TD]1/23/2013[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]ben[/TD]
[TD]1/23/2013[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi.

Is this right?....

Excel Workbook
ABCDE
1Studentdatestat1stat2
2ben23/01/201310
3steve23/01/201311
4ben23/01/201301
5dave23/01/201320
6bill23/01/201310
7ben23/01/201310
8ben23/01/201321
9ben23/01/201320
10
Sheet1


Results......

Excel Workbook
ABC
1Student2012-2013
2Ben6
3Steve1
4dave2
5
Master


Copy the formual down and change the sheet name to suit.

I hope that works!!

Ak
 
Upvote 0
I tried to do that but it doesnt add up anything it just says 0. which is not right, so i guess its a lost cuase because nothing seem to work. I have referenced the right cells and range and it doesnt work that fx you gave me is to hard to convert with different cell numbers sheet names. So i hope someone can help. all i need to do is have a fx that makes if ben add up stats in one column and show in another sheet is it too much to ask??? thanks for your help though!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Converting formula to use spill method
Replies
7
Views
204
Eric W
Eric W
N
  • Question Question
Automatically Copy Data to Another Sheet and Then Sort and Subtotal
Replies
8
Views
363
nolass
N
J
  • Solved
Data pulled from another spreadsheet not refreshing
Replies
1
Views
224
Joe4
Joe4
epardo87
  • Question Question
Dynamic Table: Progressive Row SUM
Replies
3
Views
379
JamesCanale
JamesCanale
E
  • Solved
Copy a certain set of data from one sheet to another depending on the date typed in
2
Replies
10
Views
507
myall_blues
myall_blues

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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