# Sumif of non contiguous cells using data and text

#### Chantilly

##### New Member
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):
 Name Total Points Bill 14 Sue 12 Richard 11
so on and so forth....

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
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??

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
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)

#### Chantilly

##### New Member
=SUMIF(\$A\$5:\$A\$15,F2,\$B\$5:\$B\$15)+SUMIF(\$C\$5:\$C\$15,F2,\$D\$5:\$D\$15)

OMG!!! That did it!!!!
Thank you thank you thank you!!! :D

#### Michael M

##### Well-known Member
Glad it worked for you...

Replies
3
Views
398
Replies
2
Views
159
Replies
4
Views
45
Replies
3
Views
44
Replies
8
Views
84

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?

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