Sumif of non contiguous cells using data and text

Chantilly

New Member
Joined
Jul 15, 2019
Messages
9
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):
NameTotal Points
Bill14
Sue12
Richard11
so on and so forth....

Example data:
Week 1
Class ATotal PointsClass BTotal Points
Bill7Jane4
Mary9Sherry7
Sue8Richard5
Steve7

Week 2
Class ATotal PointsClass BTotal Points
Bill7Jane8
Mary5Sherry3
Sue4Richard6
Steve8

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
Formula I tried resulting in error of too few arguments on actual sheet:
=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!!!! :)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,143
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What about, you will need to change the ranges to suit...
Book1
ABCDEFG
1NameTotal Points
2Example data:Bill14
3Week 1Sue12
4Class ATotal PointsClass BTotal PointsRichard11
5Bill7Jane4
6Mary9Sherry7
7Sue8Richard5
8Steve7
9
10Week 2
11Class ATotal PointsClass BTotal Points
12Bill7Jane8
13Mary5Sherry3
14Sue4Richard6
15Steve8
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=SUMIF($A$5:$A$15,F2,$B$5:$B$15)+SUMIF($C$5:$C$15,F2,$D$5:$D$15)
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,143
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad it worked for you...🍻
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,230
Members
416,963
Latest member
samfuge

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
Top