# SUMIFS with Criteria From Same Column

#### crockcw

##### New Member
The information I am trying to pull is the total salary of employees who have a seniority date within a certain range.

I have tried nested if statements, sumifs, sumproduct and I can't seem to get anything to work. The closest I got was using the formula =sum(sumifs("sumrange","criteriarange",{list multiple criteria here})). However, this is an "or" statement and it pulls over too much information for me. I really need this in an "and" statement format.

Example:

Need to return the salary for any employee who has between 8 to 9 years of seniority. So I need to have the criteria match ">=8" and "<9".

The seniority years column is calcualted using the DAYS formula tied to the current date. Any help would be much appreciated!!

 Seniority Years Hourly Rate 9.40 23.49 20.58 24.67 5.12 21.14 23.84 25.75 8.50 23.94 2.70 19.15
<colgroup><col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="50" style="width: 38pt; mso-width-source: userset; mso-width-alt: 1828;"> <tbody> </tbody>

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### 63falcondude

##### Well-known Member
How's this?

Excel 2010
ABCD
1Seniority YearsHourly RateSum
29.423.4923.94
320.5824.67
45.1221.14
523.8425.75
68.523.94
72.719.15
Sheet1
Cell Formulas
RangeFormula
D2=SUMIFS(B:B,A:A,">=8",A:A,"<9")

#### FDibbins

##### Well-known Member
if you used sumifS() that should have worked. Below are 2 ways to do this...

 M​ N​ O​ P​ Q​ 1​ Seniority Years Hourly Rate Min 8​ 2​ 9.4​ 23.49 Max 9​ 3​ 20.58​ 24.67 Total 23.94​ 4​ 5.12​ 21.14 23.94​ 5​ 23.84​ 25.75 6​ 8.5​ 23.94 7​ 2.7​ 19.15

Q3=SUMIFS(N:N,M:M,">="&\$Q\$1,M:M,"<"&Q2)
This uses references for the years to give you more flexibility
OR, hard-coded
Q4=SUMIFS(N:N,M:M,">=8",M:M,"<9")

#### crockcw

##### New Member
I have no idea what I was doing wrong...I kept getting 0 before. That is when I moved onto the next solution. Now that I am back to sumifs it seems to be working. I must of had a slow moment before! Thank you!

#### FDibbins

##### Well-known Member
As long as you got what you wanted, that's all that matters

Replies
8
Views
595
Replies
1
Views
397
Replies
2
Views
7K
Replies
1
Views
393
Replies
1
Views
390

1,191,690
Messages
5,988,107
Members
440,126
Latest member
duque00

### 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.

### Which adblocker are you using?

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

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