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