Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel 07: SUMIF Question

Posted on 2013-01-21
Medium Priority
402 Views
Hi, I am working on a little excel program and have to use the sumif statement.  I am not great with this function, but will provide background information.   I am trying to track leaves of absences at work and for particular employees, they get a certain number of hours per year.  The problem is, the time does not reset at year end, rather it is a 12 month rolling.  So, the manager would enter the starting balance, but I need to calculate the current balance using a 12 months rolling point of view.   To get the check date to see if the hours should count or not, I entered the current date in column Y1 by using the function  =today()   Then below it in Y4, I did a simple =Y1-365.   So essentially, my Y4 is the cutoff date for being greater than or less than a year old

Ideally, I want the sumif statement to take the current balance (G12) and subtract the sum of hours from column C, but only when the date from column B is less than a year old compared to the current date. This is the code I've tried to use and it doesn't work.

=G12-(SUMIF(B18:B70),>Y4,C18:C70)

where:

G12 = starting balance
B18:B70 = Absence dates
C18:C70 =  Absence hours

Starting Balance:                        132.4
Current Balance:                        124.4

Date of Absence      Hours Used      comments/Notes
1/1/2012                    0
2/1/2012            8
0
Question by:akatz66
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 24

Expert Comment

ID: 38801791
You likely need to use SUMIFS

Can you provide a sample workbook with dummy data to give a more tailored answer.
0

LVL 13

Accepted Solution

Shanan212 earned 1000 total points
ID: 38801809
``````=G12-SUMIF((\$B\$18:\$B\$70),">"&(TODAY()-365),\$C\$18:\$C\$70)
``````
0

LVL 50

Assisted Solution

barry houdini earned 1000 total points
ID: 38801880
You can use Y4 in SUMIF like this

=G12-SUMIF(B18:B70,">"&Y4,C18:C70)

regards, barry
0

Author Comment

ID: 38802075
I've requested that this question be deleted for the following reason:

I was able to figure out the code myself. Thanks!
0

LVL 13

Expert Comment

ID: 38802076
Could you please post the solution here as well? Thanks!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, tâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month5 days, 20 hours left to enroll