Hello,
Apologies if this has already been answered in here, I searched through a number of threads and was not able to find what I was looking for!
I am trying to sum data in a separate table within a sheet that is based on text and sums of non-contiguous cells. (I should also mention there are blank cells within the range)
Goal: Sum totals within the sheet from several different tables according to name (each name, however, is an independent cell under the column title of the class).
Using the example of the tables below, what I am trying to do is to create a separate table that lists all the names and then sums their total points from each week. (Please note, my actual spreadsheet has many more classes, names and weeks)
Example of what I want (based on the example tables below):
so on and so forth....
Example data:
I have tried using SUMIF function, but because the cells for points to be added are non contiguous, I get a 0 value. If I try and separate by commas or brackets for each week's table, then I get an error that I have too many ranges. If I then try and do SUMIFS to allow for more data, I then get the error of not enough data because even though I have multiple ranges, I only have a single criteria - that being the name.
I'm not a beginner with excel, but I'm definitely not an expert and for the life of me cannot seem to figure out the proper syntax of the function to get this information. I'm sure I'm doing stuff wrong, but just not sure what and how to fix it!!
Formula I tried resulting in "0" on actual sheet:
=SUMIF(A1:AC30,AK2,AC:AC)
=SUMIFS(A1:AC30),AK2,AC2:AC8,AC12:AC18,AC22:AC29
For the purpose of this example, my SUMIF formula that returned a 0 result would be:
=SUMIF(A1:D13,E2,B1:B13)
And the SUMIFS would be:
=SUMIFS(A1:D13),E2,B3:B6,B10:B13
*and for the example of Richard in the total tally, who is in a different class from the other 2 and therefore, points are tallied in a different column, would be adjusted according to that column of data...I assume??
Please help!!!!
T.I.A!!!!
Apologies if this has already been answered in here, I searched through a number of threads and was not able to find what I was looking for!
I am trying to sum data in a separate table within a sheet that is based on text and sums of non-contiguous cells. (I should also mention there are blank cells within the range)
Goal: Sum totals within the sheet from several different tables according to name (each name, however, is an independent cell under the column title of the class).
Using the example of the tables below, what I am trying to do is to create a separate table that lists all the names and then sums their total points from each week. (Please note, my actual spreadsheet has many more classes, names and weeks)
Example of what I want (based on the example tables below):
| Name | Total Points |
| Bill | 14 |
| Sue | 12 |
| Richard | 11 |
Example data:
| Week 1 | |||
| Class A | Total Points | Class B | Total Points |
| Bill | 7 | Jane | 4 |
| Mary | 9 | Sherry | 7 |
| Sue | 8 | Richard | 5 |
| Steve | 7 |
| Week 2 | |||
| Class A | Total Points | Class B | Total Points |
| Bill | 7 | Jane | 8 |
| Mary | 5 | Sherry | 3 |
| Sue | 4 | Richard | 6 |
| Steve | 8 |
I have tried using SUMIF function, but because the cells for points to be added are non contiguous, I get a 0 value. If I try and separate by commas or brackets for each week's table, then I get an error that I have too many ranges. If I then try and do SUMIFS to allow for more data, I then get the error of not enough data because even though I have multiple ranges, I only have a single criteria - that being the name.
I'm not a beginner with excel, but I'm definitely not an expert and for the life of me cannot seem to figure out the proper syntax of the function to get this information. I'm sure I'm doing stuff wrong, but just not sure what and how to fix it!!
Formula I tried resulting in "0" on actual sheet:
=SUMIF(A1:AC30,AK2,AC:AC)
- A1:AC30 is the range of all my cells on the sheet to be used for the data reference
- AK2 is the cell of the column containing the "name" I want to search for within the table to pull data
- AC:AC is the column which contains the "Total Points" for each student in this particular class, but is non-contiguous
=SUMIFS(A1:AC30),AK2,AC2:AC8,AC12:AC18,AC22:AC29
For the purpose of this example, my SUMIF formula that returned a 0 result would be:
=SUMIF(A1:D13,E2,B1:B13)
And the SUMIFS would be:
=SUMIFS(A1:D13),E2,B3:B6,B10:B13
*and for the example of Richard in the total tally, who is in a different class from the other 2 and therefore, points are tallied in a different column, would be adjusted according to that column of data...I assume??
Please help!!!!
T.I.A!!!!
