Tracker
HOME    TRACKER    Excel

# Sumifs Date Function

## Is there an easier way to construct the formula in the sales revenue col of the monthly analysis table on the summary tab so that one does not have to enter the specific dates into the formula?!

Related Forum Messages:
SUMIFS Formula With LEFT Function
have this formula..

=SUMIFS(Sheet2!\$D\$2847:\$D\$3065;Sheet2!\$I\$2847:\$I\$3065;\$B\$6;Sheet2!\$G\$2847:\$G\$3065;\$C\$6;Sheet2!\$B\$2847:\$B\$3065;A14)

D2847 - D3065

includes Numbers..

lets say.. i want every cell counted which has in these range the first two numbers..

every cell has..

5052
5053
5054
4060
4050

so i count the entries which has 50.. so in total it gives a result of 3. Not summing them, just count the entries that has the first two numbers "50" in every row..

The Red Markings can be replaced, the rest should stay.. it is needed due different criteria.....

SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula:

{=SUMIFS(range to sum, range to evaluate, evaluation criterion)}

The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999.

So my formula looks like this:

{=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)}

This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?

DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:

=IF(A1=TODAY()-14,"True","False")

but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

Sumifs With Iserror?
I have the formula below which sums range dv77:dv81, if dy77:dy81 is not an error. Works perfectly.

={SUM(IF(ISERROR(DY77:DY81),"",DV77:DV81))}

However I cannot seem to modify it correctly to work with a sumifs formula. Could someone please show me how it might work?, or a workaround?

For reference the sumifs would sum range(DV77:DV81), if range(E77:E81) is "Neutrogena" and range (A77:A81) is "9".

Using TODAY() In SUMIFS
Using TODAY() in SUMIFS
so, this formula works as expected:

SUMIFS Alternative
I've been looking for an alternative to SUMIFS as I have a sheet I need backwardly compatible with Excel 2003. I've looked at SUMPRODUCT which I understand is a good alternative to COUNTIFS but does not allow for summing. I've now moved on to looking at an array formula I picked up from an internet search, but it's not working properly for me. The crux of my Excel 2007 formula is:

Sumifs Formula Returning Zero
=AVERAGEIFS(\$D\$106:\$D\$2825,\$D\$106:\$D\$2825,">"&\$I117,\$D\$106:\$D\$2825,"<"&\$J117)

Returns = 0

But on a copy of the same worksheet, the same formula returns the correct value. Is there something wrong with my formula cotext?

Return Blank Instead Of \$0.00 In Sumifs
Shortened for example;
I have created a drop down list with gas, groceries and travel as the categories.
column a is labeled expenses and formatted as currency.
column b is labeled category with the drop down list in each cell.

column d contains each item, gas, groceries and travel on separate rows.
column e will contain the total for each item in column d using "sumifs formula".

If there are no travel expenses, instead of returning \$0.00 I want the cell to be blank.
I know how to do this in a simple sum function but sumifs are sumthin else.
I have tried using <> for not equal to and everything else I can think of and I usually get a VALUE error.
how do I get it to be blank instead of \$0.00???

Using SUMIFS On Visible Lines Only
I have a large table, lots of rows and lots of columns that has material (Sand, stone, etc.) batched information. I wanted to be able to allow the user to use filters to select which data they want to view, then be able to review some summary information (totals by hour of the day, totals by material, etc.) on other sheets.

I had it working really well and fast using DSUM and/or SUBTOTAL, but these functions don't exclude non-visible (Filtered) lines in a list.

Using some previous posts I saw on this site, I was able to come up with something that works, but it is extremely slow.

What I need to do is to take a list of records on a sheet that contain up to 30 material columns of information (Target and Actual batch weight amounts.) I need to group each of these lines by material into a relative time bucket (12:00 AM to 12:59 AM, 1:00 AM to 1:59 AM, etc.) The user can of course select with Time Bucket they want to view

I am using the sumifs function with two critieria ( i.e. Time >=12:00 and Time < 1:00 ) to get the hourly buckets.

You can also see from this code that I have to count the number of lines for the data and each criteria value because I could not figure out how to set the range of "B" & "C" without counting. Since I am using a filter, I know that the ranges (from a row perspective) of A,B & C are the same, just the relative columns are different. I tried to use the offset function, (i.e. Range(B) = Range(A).Offset(-1,0).Select or B = A.offset(-1,0).Select but they failed for some reason, and no error messages either. I think I somehow turned the erroring off.

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
'Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else

Use Forumula As Criteria For Sumifs?
I'm trying to sort out an accounts receivable ageing into various age categories For example, for a customer with 3 months credit term, i've used the formula =SUMIFS(\$C3:\$AA3,\$C\$1:\$AA\$1,">=4",\$C\$1:\$AA\$1,"<=9") to sum up amounts that are 1 to 6 months past due. For the next age category 7 to 12 months past due i've used =SUMIFS(\$C3:\$AA3,\$C\$1:\$AA\$1,">=10",\$C\$1:\$AA\$1,"<=15").

As my customers have different credit terms (30 days, 60 days etc), i've to adjust the above formula accordingly e.g. instead of ">=4", I'll have to change the criterion to ">=7". This is time consuming as i've got a long customer list.

Sumifs Vs Pivot Tables
I read in your "best Practice..." sticked above that it's better to use Pivot tables then multiplied criteria SUMPRODUCTs (or array formulas). i agreed with it because i found it took long time to recalculate the sheet. do you suggest it refering the new SUMIFS formula?. i found that a SUMIFS that refers to Tables can be replacement for GETPIVOTDATA. In my sheet there are several dozens of GETPIVOTDATA.

Mix Of The Vlookup And Sumifs Functions
need a mix of the vlookup and sumifs functions! (well I think at least!)

I want to find Cell A in Column 1 AND Cell B in Column 2 (like a sumifs) and then return me a value in a range (as a normal vlookup would)

Basically a vlookup matching two cells/columns rather than one

Sumifs With Index/Match Column
I have used this technique before with a sumif formula, to use the column that matches a reference cell but I cannot get this to work on a sumifs formula. What I have used previously in a sumif formula (only 1 criteria) is below.

SUMIFS - Life Ruining #VALUE! Message...
I have no idea why I am getting the #VALUE! message...

Any light shed on this would be much appricated, or an alernative formula.

Convert SUMIFS Formula In 2003
How would I convert the following formula into Excel 2003?

=SUMIFS(\$Q\$7:\$Q\$5000,\$O\$7:\$O\$5000,"Cat",\$P\$7:\$P\$5000,"Dog")

Convert Sumifs In 2007 To Something In 2003
Here is what I have

=_xlfn.SUMIFS('COMMISSIONABLE INTERNAL SALES'!\$N:\$N,'COMMISSIONABLE INTERNAL SALES'!\$A:\$A,\$A\$1,'COMMISSIONABLE INTERNAL SALES'!\$O:\$O,I\$1,'COMMISSIONABLE INTERNAL SALES'!\$D:\$D,\$A2)

I need it converted to something usable in 2003

Convert SUMIFS Formula For 2003
Need convert the following formula so that it would work in Excel 2003: =SUMIFS(\$BB\$4:\$BB\$500,\$E\$4:\$E\$500,"Abbeywood First",\$L\$4:\$L\$500,"Autumn"). I could play around with it for hours and get nowhere, so I'll just turn to the experts.

Trying To Use SUMIFS To Sum A Column Based On Two Conditions
I would like to use a SUMIFS statement to sum up a column of numbers based on two conditions. The first condition is a word. I have this done. The second condition is going to be a two week date range, which I am having problems solving.

Here is my formula that works so far.
=SUMIF(Data!C:C,"Office",Data!D:D)

Now I would like to SUMIF a second condition is met. The second condition is a date and time in this format.
1/9/2010 9:21:49 AM

I am only concerned about the date. Time does not matter. I cannot figure out how to specify a date range for the second condition. I am trying to specify between 1/1/2010 and 1/14/2010 as a test. So here is what I have so far with a SUMIFS.

=SUMIFS(Data!D:D,Data!C:C,"Office",Data!A:A,"Cannot figure out how to specify dates")

Maybe my logic is wrong...maybe I need to do three conditions? Where column A has a date < 1/14/2010 and a condition that specifies a date > 1/1/2010.

Lookup Worksheets & Do Sumifs Criteria
Could ANYONE help me solve this formula based on critera sheet name.

On my main sheet workbook i have all the sheet names in column A with their description in column B. In column C i would like to have sort of this formula with result as total.
eg....

Exclude Specific Parameters In A Column While Using SUMIFS
I have a SUMIFS function written that reads where some of my budget is coming from. I track several different departments but am only responsible for some of the departments' budgets. So i need to exclude two of the departments when the SUMIFS is reading the other parameters from my list.

From the example i attached, how would i go about writing the formula in I3 to skip the Cost if it comes from Dep 2 and Dep 4?

Multiple Criterieas Using Sumifs And Countifs Not Working
I am unable to get the correct results using Sumifs and Countifs function. Below is an example:

If I use the formula as =IF(ISERROR(SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9),"N/A",SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9)

I get the result as 0 but the result i want is 19.67% (1234 /6275). Since i have multiple criterias which might not be available at times in the data, i get the value as 0.

BCDEF2CountryMain Conseq.Other conseq.Product Type Amount 3AustriaInternalNoneInt2B \$ 278 4AustriaExternalNoneExt3C \$ 900 5AustriaExternal-8A \$ 2,388 6NorwayInternal-MV \$ 567 7AfricaExternal-SY \$ 1,234 8AfricaInternalNoneIntJN \$ 908 9TOTAL \$ 6,275

Similarly i have used a formula using Countifs; =IF(iserror(countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9),"N/A",countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9)

And even the above formula does not work with the criterias given. What i require is, if the country is "Austria", "External", and "NoneExt" then count those lines which has product Type as "3C", 2B". So the result for the above should be 1.

Request you to kindly let me know the correct way or the right formula to extract data for the above criterias.

Sumifs Value Returning: Return Monthly Values
I have a couple Sumifs formulas that I am trying to return monthly values for but there are small discrepancies on the values that they return. When I manually select the cells that I'm targeting with the formulas, I get a different value. I have checked the formulas 100x & can't figure out what is wrong with them.

Example:..............

Sumifs Won't Work For Multiple Tabs
Ok here is my dilemma, I am trying to calculate gains and losses by organization. I created a sumifs formula that works on a single page, but it doesn't work on multiple tabs on my summary page.

Here is the data:
HRS DEVIATEDTAILORG0.2OPS-0.3OPS0.8MX0.5OPS-2.5MX-1.5WX-1.5WX

Here is the formula on the same page as data:
=SUMIFS(A26:A40,C26:C40,"wx",A26:A40,"

SUMIFS Formula To Work Out If A Rep Is Below Target On Selling Brands
i am trying to us the SUMIFS formula to work out if a rep is below target on selling brands.

I have two worksheets summary and data, Summary worksheet is where i am trying to do the sumif formula against a sales average taking the information from the data worksheet
I have account manager name, brand, sales, DSI
I tryed the below sumifs formula but its coming back as 0

Please could someone have a look and point out the error in my ways
=COUNTIFS(data!\$A\$2:\$A\$11,B17,data!F2:F11,"<B2)")
The first count if should count how many times the account manager appears in the brand and the second count is if his DSI on the data worksheet is higher that the target dsi on the summary sheet

Multiple SUMIFS (find And Add Up All The Total Requests For Only The Summer Period (Column Q))
I have a list of about 2,500 entries containing information of all my works leave requests for 2009. This list contains more than one entry per employee, as it shows all their individual requests for different weeks. The list is compiled pulling through data from other worksheets. It looks like this...

Sheet Name: MASTER

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column E = Week ending
Column F to L = Days of the week
Column M to O = Request Type
Column P = Number of days requested for the week

I then have another sheet (Called REQUESTS) with all the employee names (1 entry for each employee), ID numbers and total requests for the year. This looks like this...

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column P = Number of days requested for the year
Column Q = Number of days requested for the summer

I know how to locate and add up all the requests for each individual for the whole year (Column P) from the MASTER sheet to the REQUESTS sheet by using the flowing formula for each employee.

=SUMIF(MASTER!\$D:\$D,D2,MASTER!\$P:\$P)

But I don't know how to ask Excel to find and add up all the total requests for only the summer period (Column Q). What I want Excel to do is this................

IF And Date Function
I have a spreadsheet which has a date in a column, day in another and an IF function related but the if function does not seem to be working, can anyone assist? here is an example of how it is setup Cell A1 has a date e.g. 31/01/2009. Cell A2 returns the day of the week function =WEEKDAY(A1,1) formatted to custom to return the DAY of the week using dddd. Cell A3 has the IF function e.g. =IF(A2="Saturday",0.1,0). Cell A3 is the PROBLEM as all this ever returns is 0 (false) and never returns 0.1.

Date Function
I am trying to figure out what function automaticly updates a date in a cell everytime the workbook is opened.

Excel Date Function
Kindly help me with the attched file.

I am trying to divide the months into weeks so I could easily identify if a particular date of a month falls on 1st,2nd,3rd or 4th week of the month.

If Function: Change In Date
i have date values in say, cells a1 to a4

20080522 21:00:00
20080523 14:45:00
20080523 15:00:00
20080523 15:15:00

i would like cell b to tell me when there is a change of date (not change of time), ie insert a 1 in lets say cell b2. i know it will be a simple if function but i dont know how to only read the first 8 digits

Find Value By Date Within Function
I am comparing two series of data, a daily series and a weekly series, to make calculations. The "F" column is daily data and the "I" is weekly.

The formula I am using is:
=IF(AND( SUM(I58:I62)<F62,I63>F63),B64,0)

The data I need to use in the "F" column in place of "F62" is always on a Friday.

I had taken "F62" which is the preceding working day's data. In fact I need to take the preceding Fridays data in column "F" as I need to compare the week to week trends. Due to bank holidays I cannot count back form F62 to get the data. There is always end of week data recorded on Fridays even though it may be a bank holiday.

I have the dates in the "H" column. Can a date reference be used in the formula to find the Friday data in column "F"? Or is there another solution to this problem?

Static Date Function
I have found threads that explain that if you want a to use the now() or today() function but make it static, you have to put a code in the worksheet relating to it.
I have read quiet a few threads, but i can't seem to make my code work.

I need to format column A to have the date format in it. I have a event procedure that puts Now() when run in Column A and I think i need to add the following code to the worksheet to make it static.

Date Function/Text
In column A there is a date and time function in the format as follows :
dd/mm/yy hh:mm:ss most of the time.

When this is downloaded it is easy to manuplite. But some times it show as
above but as a text string or sometimes the dd/mm is switched to mm/dd.

Does anyone know the work rounds these formulas?

I use the dd/mm and Hr function to complie reports from column A but usually
spend hours using mid() left() function due to the date change.

I have tried formating the cell but this does not work?

Date Function Is Not Working..
I have set of data in which i want to put filter Milestones Type <> Milestone
and a filter Actual date is greater than or equal to 30 days ago and is less than or equal to today.

Date Function, For A Tracker
I've entered there name in column A, and the expiry date in column B. How do i then get column C to show how many days or months are remaining? Ideally i would have the guys with 3months or more left in green 1-2months amber and <1month in red.

IF,AND, Function Displaying Last Date
I have been trying to get this formula to work, and it appears it only works when it wants to and I cannot figure out why. What I'm trying to do: I have a spreadsheet with several columns of data, but I want the formula to read only two entire columns for a specific criteria for each column, then display the most recent date based off the criteria. I do not need to total up the criteria, just display the last date. This is what I have so far: =IF((AND(Sheet2!A:A=B2,Sheet2!B:B="Game: Counter Strike - Source")),MAX(Sheet2!C:C)).

I have this in a table, header as "last date" which is (C2), the B2 in my table would be where criteria would be typed in. So basically, my table should pull the last date from based off the criteria from raw data in sheet2, that is entered in to B2 on sheet1 and is "Game: Counter Strike - Source".

I have the following formula in a cell

=LOOKUP(WEEKDAY(A1),D2:D8,C2:C8)&A1

that I want to look up the Day (mon, tue, etc) and then return the date entered in cell A1

So if the date in cell A1 is 01/05/08, the formula should return Thu01/05/08.
Currently it returns Thu39569, even though the cell is formatted as a date format... How do I get it to return the date in a date format?

AND Function Date Range Lookup
I am trying to an If statement but the first part of the IF statement needs to be between a specific date range. Here is the formula I have:

=IF(AND(Sheet1!A4>=1/1/2008,Sheet1!A4

Subtracting Time/date Using NOW Function
I have a working week that starts at 07:00 on a Monday morning and finishes at 14:30 on a Friday afternoon.

For planning purposes I need to know at any given point how many working hours are left. The reason for this is so that I can multiply that figure by the amount of Engineers available which will give me the amount of Man Hrs left in the week.

For example - if it is 11:00am on the Tuesday, how many working hours are left ....

IsDate Function Looking For American Date
Got a random problem with the IsDate function and was wondering if anybody could help?

Below is the code I am using to check a user input of a date, and if it is a recognised date put it in to a standard format (dd/mm/yyyy). The problem is that VBA is checking for an American ...

AVERAGE IF Function With Date And Characteristic
I will attempt to make this as easy as possible in explaining something I am looking to do. I am looking to Calculate the average lifespan of an individual based upon

1) They are "dead" (A status,either "Alive" or "Dead" found in Data!F2:F653)
2) They were born between 1/1/2007 and 12/31/2007 (Delivery Date provided in Data!C2:C653)

The lifespans were provided on Data!E2:E653

Function To Clean Up Date/timestamp
I have date/time data in the following form:

Mon Jan 12 07:26:26 EST 2009

I have tried multiple forms of LEFT, RIGHT, MID, etc. Date & Month are always three characters, but the date ("12" in this example) can vary between 1 or 2 characters.

Date Function That Avoids Weekends
I just want to ask if we can create a formula that will not go to saturdays and sundays. example :

I use =today()+3 so it means if today is tuesday the result is friday, if im going to open it tomorrow the result will adjust to saturday if that happens i want the day to be automatically go back on friday, the date itself, so it means i'll just wait on friday to get the result as "monday". It just avoid saturdays and sundays,. Is that possible? By the way, format would be m/d/yyyy.

Date Function (conditional Formatting)
I would like a date in a cell to flag up in a colour (say, red) once the date has expired by a certain period (e.g 7 days). How can I do this (conditional format).

Date Calculation - Datedif Function
I am trying to subtract two dates to find out whether an invoice is 6 months past due (regardless of number of days). I use DATEDIF in my formula and it works fine until now. It seems the function takes number of days into account and won't return the desire result when there are 31 days. I want to find out whether the number of months between two date are greater than or equal to 6 months without considering the number of days. I am attaching a sample worksheet for better explanation. As you can see, October is not working right.

I am trying to format this function to just return the year, but I can't seem to get it to work. It just returns the full date and all I need is the year.

Function To Show What Season A Date Falls In
I am trying to make a function which will take a date as input and display the season for that date. i.e. the input 1-Jan-1992 will return "Summer" (southern hemisphere seasons)

Function That Enters The Corrent Date Automatically
I want to know if there is a function that enters the corrent date automatically. E.g., if I enter "3000" in B1, the result will be "2/9/2008" in, say, B2.

Can it be done?

WORKDAY Function To Calculate The Start Date
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?

For example,

Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009

Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days

The Start Date should be 30 May 2009.

Seems like I am not able to use WORKDAY function to calculate the start date.

Vb Or Function: Reset Vacation On Anniversary Date
SOLVED: adjusted functions. creating a vba macro for other reset goals. other goals have changed as well.

I have a table where the subsheets have the specific days that an employee uses vacation, sick, personal or other hours of off time. i wrote alot here to get my goals/thoughts down

my goal is to master one section then apply it to the other vacation date types. i'm stuck on going with a formula or using vb. also another concern is odd months such as someone hired 10/31 compared to a month like 6/30. not sure if this is a real concern.

a. need to reset vacation hours for the year based on annual anniversary date
-"vacation hours per year"
-"vacation hours remaining" =E8-('Vacation 1'!B8+'Vacation 2'!B8)
this data is taken from the subsheets. there is vacation 1 for jan-jun and vacation 2 for jul-dec

1year=40 hours
after 1 year anniversary =80 hours
3 years+ =120 hours
Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed)

second part of my project:
b. vacation hours remaining can be carried over up until 3 months after their anniversary date. at this point they are wiped back to 0 until the next anniversary date (where the carry over hours recalculate)

ATTACHED TEMPLATE

things i am changing in design:
vacation hours per year (to include a formula to calculate anniversary date, with the amount given for 0 year, 1 year, 3 years....) rather than a manual added # (which was lets say 40 hours+ carry over hours)
-thats why i am separating the carry over hours... which i'm hoping to auto calculate by taking

anniversary date - 3 months in a statement to reset it back to 0 after the three month period.

SUMPRODUCT Function With Date Calculation Criteria
I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date - purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below.

One cell should contain the aggregate security purchase amount whose term is 1-90 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.