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

[TABLE="width: 104"]
<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>[TR]
[TD="class: xl73, width: 88, bgcolor: #C0C9F1"]Seniority Years[/TD]
[TD="class: xl74, width: 50, bgcolor: #C0C9F1"] Hourly Rate [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent, align: right"]9.40[/TD]
[TD="class: xl68, bgcolor: transparent"] 23.49 [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent, align: right"]20.58[/TD]
[TD="class: xl68, bgcolor: transparent"] 24.67 [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent, align: right"]5.12[/TD]
[TD="class: xl68, bgcolor: transparent"] 21.14 [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent, align: right"]23.84[/TD]
[TD="class: xl68, bgcolor: transparent"] 25.75 [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent, align: right"]8.50[/TD]
[TD="class: xl68, bgcolor: transparent"] 23.94 [/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent, align: right"]2.70[/TD]
[TD="class: xl68, bgcolor: transparent"] 19.15 [/TD]
[/TR]
</tbody>[/TABLE]

 

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

[Table="width:, class:grid"][tr][td] [/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][/tr]
[tr][td]

1​

[/td][td]Seniority Years[/td][td]Hourly Rate[/td][td][/td][td]Min[/td][td]

8​

[/td][/tr]

[tr][td]

2​

[/td][td]

9.4​

[/td][td]23.49[/td][td][/td][td]Max[/td][td]

9​

[/td][/tr]

[tr][td]

3​

[/td][td]

20.58​

[/td][td]24.67[/td][td][/td][td]Total[/td][td]

23.94​

[/td][/tr]

[tr][td]

4​

[/td][td]

5.12​

[/td][td]21.14[/td][td][/td][td][/td][td]

23.94​

[/td][/tr]

[tr][td]

5​

[/td][td]

23.84​

[/td][td]25.75[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]

8.5​

[/td][td]23.94[/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

2.7​

[/td][td]19.15[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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