# Sumifs Date Function

Nov 20, 2009Is 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?!

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

I am trying to use the sumifs function to add a column that is in pounds but with multiple criteria.I am trying to add a price field but between two dates (between the beginning and end of each month) another criteria from a range of cells I have copied the formula I have written below

=SUMIFS(Sheet1!K:K,Sheet1!B:B,">="&I17,Sheet1!B:B,"<="&J17,Sheet1!W:W,"="&M17)

try to explain a little column K is the column I need to sum column B is the date field that I need to extract i.e. 1st to 31st January (I have put these dates in a cell that correspond to I17 and J17)

then the last bit column W is another criteria that I need met, this works for just one cell however if I try to add another criteria in another cell it doesn't work after M17 I add to the formula ,Sheet1!W:W,"="&M18)

Excel.xlsx

Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is

=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)

However it is giving a value of zero only. I have tried other combinations as well.

I need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.

I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.

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

When I need to return numerical data with multiple criteria, I use the SUMIFS and it works fine. But when I need to return a text string, the function is not called for. What function can I use to make this happen? I've tried SUMPRODUCT, which I've had success with in the past, but it's not working this time. Maybe I'm laying it out wrong, but this is how I currently have it:

=SUMPRODUCT(column of text to be returned,((column of criteria1)=criteria1)*((column of criteria2)=criteria2))

Do I need to fix this formula or use something else?

In a workbook I have a tab with multiple columns containing names, activity codes, billed hours, etc. On another tab I have created a budget for which I use the Excel SUMIFS function to check the database in the first tab for relevant entries and add them together in the budget sheet.

Now here's for the tricky part. On the budget sheet the names are not mentioned, only the job titles. So these are grouped together. This means that the first tab shows only names, while the budget tab only shows the function title.

The function I have used thus far is this:

Formula: [Code] ......

The first part refers to the column containing the billed hours, the second part refers to the column containing the names (criteria range 1), the third part refers to a specific name (criteria 1), the fourth part refers to the column containing the activity codes (criteria range 2), while the fifth part refers to a specific activity code (criteria 2).

This works really well as long as the budget tab only mentions the names, yet it only aggegates at the job title level. Would it be possible to use the same SUMIFS function to search the database for, say, all the names that fall within one specific job title? Essentially that would result in this somewhat bulky function:

Formula: [Code] .....

Clearly, Excel doesn't allow this.

I know you can't use the SUMIFS function in 2003 but give alternative in 2003

=SUMIFS($D$3:$D$30000,$C$3:$C$30000,"IOP",$E$3:$E$30000,">=01/03/2011",,"

I'm trying to recreate a formula in VBA, but struggling with the "greater than /equal to start date" and "less than / equal to end date" parts

this formula works ok in the spreadsheet:

Code:

=SUMIFS(bbHours,empCodeList,thisEmployeeID,bbDate,">="&startDate,bbDate,"=" & startDate, Range("bbDate"), "=" section, but unclear how to handle this. It must be handled entirely via VBA, not formulas

Note: I've adjusted certain key names / VBA strings for clarity, everything is defined correctly

I have time-worked spreadsheet use to calculate the hours worked over a year, now I need to sum the hours worked during a given period if they happen to be on a public holiday. I have a column (say A) displaying dates from 1/1/13 till 31/12/13, another column giving the result of the worked hours (say B). I also have a defined list of public holidays which I need to compare with (named Holidays). The working year is "divided" in 13 periods of 28 days each (the last one having 29 days). I was trying to use the SUMIFS function (since i have other constrains that need to be met)

e.g. =SUMIFS(B1:B28,A1:A28,Holidays)

Let's say I have a simple table like below:

Region

Date

Qty

N. America

23/4/2014

10

EMEA

24/4/2014

50

EMEA

25/5/2014

20

I want to sum up all EMEA quantities that are in the month of April.

I've tried this:

SUMIFS(C:C,A:A,"=EMEA",MONTH(B:B),"=4")

I've also tried:

{=SUMPRODUCT(IF(A:A="EMEA",1,0)*IF(MONTH(B:B)=4,1,0)*C:C)}

Both do not work.

Also as the data grows each month, I would want to refer to the entire column in my formula, e.g. 'Region' as A:A (rather than A2:A4).

Is it possible to use Sumifs to calculate multiple columns i.e. if a summary were able to sum Part No. 742810 O-rings for March it would arrive at 24 O-rings and 20 of the same for April. I tried it using

=SUMIFS(Issues!$F$2:$F$6,Issues!$B$2:$B$6,B8)

(Example) but it seems that Sumifs may have restrictions on the number of columns.

******** language="JavaScript" ************************************************************************>

Microsoft Excel - Parts Distribution.xlsm___Running: 14.0 :

OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL2L3L4L5=ABCDEFGHIJKL1PartsÂ Description PartÂ No.EquipmentMar1Mar5Mar12Mar30Apr6Apr17Apr27May3

[Code] .........

Is this is just an example, but the logic should hold true. Data for example is in A1:C5. I want to use a SUMIFS statement that will look at type of Vehicle and Dates. So for the example in cell D1 (I wanted to add only Cars sold between May 1-2), but my current equation = 0, where I am expecting 20

=SUMIFS(C1:C5,B1:B5,B3:B4,A1:A5,A1)

I'm trying to create a sumifs that has 'before date X' and 'on or after date Y' as two of the criteria.

In the past, I have simple done a DATE(x,y,z) function inside the sumifs, but I'm trying to change this for reasons that would take a little while to get into. Let's just say it would make my coworkers lives much easier.

My hope is to get these date range criteria by referring to ribbons which have the dates in mind in them. Here is the formula I have now, and an example. See the red part of the formula.

Say I'm trying to have before Aug 1, 14 and on/after July 1, 14 as two criteria. FBP column A is where I would have the dates that this criteria would search through:

B1: 7/1/14

C1: 8/1/14

Formula: =SUMIFS(FBP!$G:$G,FBP!$B:$B,"=E",FBP!$A:$A,">="&B1,FBP!$A:$A,"

Figuring out a SUMIF or SUMIFS formula which will clean up some weekly data. I am envisioning a SUMIF formula which looks at the client name in column A in a table and then it will search through the long list of data for all entries for that specific client on another sheet in column "A", for instance. It needs to take into consideration only the encounters which happened between the dates in the table for that client listed in column B & C. The sum will be the column next to the column with each client's name which has a procedure date in between the date criteria's from the table. I have attached an example to better illustrate.

SUMIF Example.xlsx

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?

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?

Trying to automate the period part of the impt function

To calculate current value of loan i have the below formula below with the 3 being the current period

=IPMT(4.3%/12,3,5*12,-7000)/(4.3%/12)

What i would like to do is for the period to be self calcuating from current date and the loan start date. I can return a value in days using start date - today() and aware month function returns the month number but stuggling to find a way to work out cumulative month from the start date.

I have these values in my excel sheet

in column A in column B

1/02/2014 1650

2/2/2014 1649

3/2/2014 1648

4/2/2014 1647

5/2/2014 1646

I would like a formula that gives me the value in column B if the date of today is equal to the date in column A.

I've tried with simple IF formulas but it doesn't really work because the formula spans over several rows.

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.

View 2 Replies View RelatedI am trying to figure out what function automaticly updates a date in a cell everytime the workbook is opened.

View 5 Replies View RelatedI'm trying to find the maximum over a set of conditional sums, preferably using an array formula or some other single cell solution. My experience with array formulas so far has been to copy things off the internet and modify slightly to my needs.

Here is how my data is set upA

B

C

D

Trait1

Trait2

Value

Trait 1 to Max over

Cat

LongHaired

3

Cat

[code]....

The "Trait1" I am summing over will remain constant for each refresh of the spreadsheet. I want to return the max value of the sum over each "Trait2" that falls into the "Trait1" category.

In this example for Trait1 of "cat" I would return 8 (5+3) that corresponds to LongHaired cat.If I were writing straight SumIf and then Maxing over I would write

Code:

=SUMIF(C:C,A:A,$D$2,B:B,"LongHaired")

for each unique Trait2 and then Max over the set of outputs.

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.

View 3 Replies View RelatedI 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?

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

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.

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?

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.

How can I take a cell with a date in it (A students birthday) and in another cell automatically show how old that student is? (2/18/03 in cell A1 and show 8 in cell A2)

View 5 Replies View RelatedI have a sub that copies a section of my sheet onto another sheet and then clears out my sheet so I can type in the sales numbers for the next day. Now I am trying to figure out how to get the date to change.

What I would like to do is to have the Macro type the date MM/DD/YYYY into cell C9. The date should be whatever date the computer clock is set to minus 1 (since we are filling out yesterday's sales). The other part that I am struggling with is making B9 appear as the specific day of the week.

The key part of this is, C9 may have the date entered in manually and if so, I would like to be sure that the day of the week matches the date entered if at all possible.

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

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.

DateAdd("yyyy", -1, Date)

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?

I am using a SumIfs formula and I want to add a criteria that only sums the Sum Range when it is between two given dates (01/01/2013 and 31/12/2013).

View 2 Replies View RelatedUsing TODAY() in SUMIFS

so, this formula works as expected:

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:

View 2 Replies View RelatedI have the following formula in Col C

=SUMIFS(C$275:C$379,$A275:$A$379,{"Idle Time","Oil stock *"})

The criteria "Idle Time" & "Oil stock losses" are in Col A.

I would like to add the values in Col C based on "Idle Time" & "Oil stock losses" which are in Col A

The formula =SUMIFS(C$275:C$379,$A275:$A$379,{"Idle Time","Oil stock *"}) adds the idle time value correctly, but ignore the values of Oil Stock losses

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

I have a spreadsheet where columns I through V are date fields (stored as text as some have letters after the date). Is there a way that I could write a formula so that Excel returns the column letter of the last cell without a letter at the end of it? So it kind of looks like this:

I J K L M

05/01/2014 05/15/2014 05/30/2014(p) 06/20/2014(p) (null)

What I want in the column with the formula then would be just the letter J. Is this even do-able?

a column contains dates in date format, contains a date before the column b date b column contains today() I want c column to get some output, if b-a is more than 5 years (exact days), say 20 do , if b-a is more than 2 years and less than 5 years, say 40 do , if b-a is less than 2 years , say 60 otherwise blank use if condition to get the above results. I have formatted a, b with date formats

View 9 Replies View RelatedADVERTISEMENT