SUMIFS with Criteria From Same Column

[crockcw]

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>

 

[63falcondude]

How's this?

Excel 2010
	A	B	C	D
1	Seniority Years	Hourly Rate		Sum
2	9.4	23.49		23.94
3	20.58	24.67
4	5.12	21.14
5	23.84	25.75
6	8.5	23.94
7	2.7	19.15
Sheet1

Cell Formulas
Range	Formula
D2	=SUMIFS(B:B,A:A,">=8",A:A,"<9")

 

[FDibbins]

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]

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]

As long as you got what you wanted, that's all that matters