SUMIFS Not Matching Yyyymm Dates Stored As Strings
Jun 21, 2014
I have a data table on worksheet "Data" which has 2 dates (start and end) stored in columns in yyyymm format: (example 201204)
I also have a row/column table which is supposed to sum up balances which match both the start date in the column, and end date in the row, so that all unique combinations of start and end dates will have their own cells.
This is an example formula I am using
VB: =SUMIFS(Data!$K$2:$K$37233,Data!$F$2:$F$37233,"="&TEXT(R$6,"yyyymm"),Data!$H$2:$H$37233,"="&TEXT($B27,"yyyymm"))
I checked individually and the TEXT(R$6,"yyyymm") as well as the ($B27,"yyyymm") does return the yyyymm format.
The problem is that excel thinks that 201204 in the data table is different from the string "201204" and I keep getting zeros for that SUMIFS formula.
View 4 Replies
ADVERTISEMENT
May 5, 2014
I do have the following problem:
When I use the SUMIF(S) function and a criteria range that has numbers stored as text (eg: '394) the unequal operator (<>) doesn't work.
=SUMIFS(B:B,C:C,"<>493") doesn't work
I'm able to get the salary of employee 493 by using "493" instead of "<>493", but it seems that with the unequal operator Excel starts treating this ID as number. The only work-around I found so far is to use a wildcard e.g. "<>*493" or "<>493*". Excel then treats it as text again, but this is dirty workaround. What happens if an employee with an ID e.g. 1493 shows up!?
"<>'493" or something doesn't work unfortuantely..
Employee ID
Salary
'250
10000
'493
13000
View 14 Replies
View Related
Aug 1, 2012
I was using SUMIFS on a large amount of data, but wasn't getting back the exact sum that I needed.
It turns out that I had cells with values similar to the following
Code:
000123
0123
Basically, text fields with the same numerical values, except for the number of leading zeros. Due to the nature of the table, the number of leading zeros is important.When I did SUMIFS, it counted the sum of the two values together. For example with the following:
Item
Batch
Qty
ABC
123
1
It appears that even if a numeric value is stored as text, SUMIFS treats it like a number.
View 3 Replies
View Related
Jun 16, 2009
I am trying to write a macro to transfer patient personal data onto a database.
On sheet 1 the user inputs the patient surname, forename, date of birth, date of test and the test results.
On sheet 2 is the database, where patient surname, forename and date of birth is stored in columns A, B and C respectively. The remainder of the columns on each row is available for scoring test results.
What I would like the macro to do is as follows:
•Read the patient surname as input on sheet 1
•Check on sheet 2 if that surname already exists
•If it does, check if the forename and date of birth also match to sheet 1 for that particular entry.
•If a match is found then copy the test results into a column on that particular row. E.g. could be copied into column D.
•If no match is found, then all the data is entered as a new row on the database (I’ve written the code for this bit but thought I should add it in for clarity)
This will then allow all test results for a particular patient to be read off the same row in the database.
View 13 Replies
View Related
Jun 14, 2008
how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?
View 5 Replies
View Related
Feb 24, 2010
I have a worksheet containing a 'first name' column and a 'last name' column. I've put together a macro that requests a first and last name, then filters on the result, or will filter on the last name only if that is all that is entered (I know this can be done using autofilter, but let's not go there!).
So far so good, but what I want to do is add a bit of code that, when a non-existent last name is entered, displays a message stating this and exits the program rather than trying to filter on the name and displaying no records.
View 4 Replies
View Related
Jan 1, 2014
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 Related
Nov 16, 2008
What formula will check a text string value in a column against a master list of text strings and advises whether or not the text string appears.
View 9 Replies
View Related
Jul 31, 2009
1. Counts the number of cells in a column with the same 18-character text string(Full String is 24) using a RIGHT( function.
2. Returns a 1 if there is 1 and a zero if the count is greater than 1.
View 3 Replies
View Related
Jun 1, 2014
I have a SUMIFS formula that I am having trouble with. I am trying to check the following:
=SUMIFS(NFCU!$G$5:$G$298,NFCU!$E$5:$E$298,NFCU!$H$2,NFCU!$A$5:$A$298,"<e1")
The range NFCU!$A$5:$A$300 is a column of dates. The evaluation of "<e1" does not seem to evaluate. However, when I change "<e1" to a date value of 41760 it works. How can I reference a field rather than the raw value?
View 4 Replies
View Related
Feb 12, 2009
I want to see if the people on list one are on list two. I need to come up with a formula that gives TRUE for each person on list one who is on list two and false for each person on list one who is not on list two. Several caveats: 1) there are people on list one who aren't on list two and vice versa, 2) the people have several defining characteristics which need to be met, and 3) dates are involved.
With respect to point three, TRUE should only appear if the people on list one are on list two and if they were employed by the organization on a specific date (ex: 3/5/1996); in order for the TRUE statement to appear in the G column, the person should not only be on list two but also be employed on a specific date. The specific date should fall within the start and end employment contract.
View 6 Replies
View Related
Nov 4, 2007
I am currently coding up a VBA macro that will counts up the number of occurrences of a particular event for a particular date. The problem is that it keeps returning zero as the result. I thought it might have been a data type mismatch issue as the cell format is a date in the mm/dd/yyyy format but the data is stored as a string so that I can easily manipulate it with the built in VBA functions. I tried changing the data type but still received zero as the result. 'a' and 'c' refer to cell ranges, criteria is a string of characters and criteria2 is a string that pulls in a date in the format of mm/dd/yyyy. I know this code works when I don't use dates but I can't figure out why it doesn't work with dates.
criteria = Application.Workbooks(FileName).Worksheets("Day_Counts").Range("B1").offset(0, column).Value
View 9 Replies
View Related
Jul 15, 2008
converting text strings to dates.
The dates I have imported into a spreadsheet are of the format:
11.05.08
I use code to convert it to this format
11/5/08
But with some of the dates, I get
11/5/2008 whereas with others, I get
11/5/08
However, if I recorded a macro the date format appears as
11/5/2008 for all the dates.
But the code is excatly the same, if I
see below:
My
For Each celld In Range([e1], [a1].End(xlToRight))
If celld.Value Like "*DATE" Then
celld.EntireColumn.Select
On Error Resume Next
Also, how do you convert strings to dates using VBA?
I thought DATEVALUE performed this function but keep getting errors when i try using it.
View 9 Replies
View Related
Apr 17, 2014
I have some dates in cells formatted as 201212 201304
How do I format the cells so I the date like mm yyyy? so 12/2012 and 04 2012?
View 2 Replies
View Related
Dec 13, 2011
I'm trying to write a function that takes a date in format YYYYMM and adds/subtracts a number of months.
for example: newDate(YYYYMM,numMonths)
newDate(201109,5) would give 201202
newDate(201102,-4) would give 201010
I was thinking i could use the left/right function to retrieve the month and year ........
View 7 Replies
View Related
Nov 11, 2013
I'm trying to create a UDF that would be able to accept a column as an input range.
Match dates contained in two other cells and return the addreses so I can perform a function between the two addresses.
like Function multiplyrange(B2,B3,A:A)
So
B2= Start Date
B3= End Date
Column A is contains all the dates
sheet set up:
A
B
1
Start Date
1/3/2013
2
End Date
1/7/2013
[Code] ........
So it basically just multiplied all the values between the dates found.
View 7 Replies
View Related
Jan 17, 2013
I have a list of chronological dates from left to right in a single row starting in H1 and going out to say BY1 or further
In a single column (C:C) I have random dates.
I want to insert a shape when the dates in column matches the chronological list of dates in (1:1).
I want the shape to be inserted on the row on the random date and the column of the matching chronological date.
I have this so far:
VB:
Sub Shape3()
'
' Shape3 Macro
[Code]....
This works OK but for some reason the shape changes when the next random date in column C is not found.
View 1 Replies
View Related
May 20, 2009
I have Column A with chronological dates. Some of them will be the same. If the dates are the same then the lowest row of those dates would need to add all the corresponding values in column D and print the total to column E
eg.
A1 through A3 = 2/12/2009
D1 through D3 would need to be added
E3 would display total for =d1:d3
it would then do this for each of the dates that match in Column A
View 4 Replies
View Related
Aug 5, 2013
I have a column of Dates in Col B with corresponding X values in Col C. I specify a start date in Cell F14 and an end date in Cell F16. I then use the function shown in cell H6 to identify the starting row number (corresponding to start date) in Cell F6. If I used the same function to identify the end row (in this case 4/2/13), it would choose row 8 which is the first row in which it encounters that date. Unfortunately, I need the end row to identify the last row that date appears in. In this case, that would be row 11.
Excel 2010ABCDEFGHIJKLM1 Date and TimeX2
3/30/201334/1/2013 1:366.9744/1/2013 4:563.95 54/1/2013 4:573.27
Start Row64/1/2013 5:165.553=MATCH(DATE(YEAR(F14),MONTH(F14),DAY(F14)),$B:$B)+174/1/2013 5:172.35
End Row84/2/2013 17:191.59894/2/2013 17:192.81 104/2/2013 17:252.14 114/2/2013 17:262.05 124/3/2013 5:152.84 134/3/2013 5:154.11
Start Row144/3/2013 5:173.2404/01/13154/3/2013 5:173.45
End Row164/3/2013 17:124.0304/02/13174/3/2013 17:133.621819Sheet1
View 7 Replies
View Related
Jun 18, 2008
I have a worksheet with a data sheet that has the values for a number of portfolios over a time period (one month)then I have a corresponding sheet for each portfolio (in the attached example I have included only portfolio 1). What I am trying to do is to make a formula in the sheet Portfolio 1 that matches the date in the Portfolio 1 sheet with the data sheet and by matching the date returning the value for the Portfolio 1 for the date ie date 2008-06-01 should return for portfolio 1 (from the data sheet) 500.
I have tried with varying Index and matching formulas and I do believe that this is the way to go.
View 3 Replies
View Related
Jan 9, 2013
i have 2 columns of number of cars using various roads, taken in the last 30 days. A macro has processed the data in these ranges and changed the outlier values to "Bad" so the formula ignores them.
However in the columns A and C , there are dates that the counts were taken on.
{=IFERROR(CORREL(IF(B4:B33"Bad";IF(D4:D33"Bad";B4:B33)));IF(B4:B33"Bad";IF(D4:D33"Bad";D4:D33)));"0")}
sometimes these dates aren't the same (no data available on christmas in some areas, boxing day in others etc. Obviously this destroys the correlation, as it puts the days out of synchronisation and the correlation falls apart.
Is there any way i can adapt the formula for it to check the dates are the same before correlating? It'd almost be like a vlookup, find the date, if match, add to calculation, if not then go to the next date. i'm happy for it to only correlate 26 of the 30 days if there are that many conflicts.
example for last 6 days performed on the 28th of december (looking for 6 calendar days up to but not including 28th dec.
22/12/2012 9 22/12/2012 Bad 23/12/2012 10 23/12/2012 22 24/12/2012 3 24/12/2012 21 25/12/2012 7 26/12/2012 18 26/12/2012 8 27/12/2012 15 27/12/2012 15 These 2 cells would be blank and this column would only contain values for 5 days.it could only correlate the 3 days of 23, 24 and 27.
Given that i'm going to be correlating this information with a number of locations, each with different reporting dates, filtering the dates before correlating and deleting unmatched dates is not a great option, but I will consider it if there is no other way.
View 9 Replies
View Related
Dec 20, 2012
I need to do a vlookup that takes a string from one cell and then tries to find that string (embedded in a larger string) in the table array
Essentially I imagine this involves the FIND function at some point.
Attached is an incredibly simplified example of what I'm looking for.
View 9 Replies
View Related
Dec 16, 2013
I have an excel worksheet with about 10K rows of data in column A.
I have also another list of data, about 200 rows of data, in column G.
I need to color each cell in column A that contains, anywere in the string, any of the data strings in column G.
Example:
in column A
row 1: info@duende.com
row 2: rasko@silvester.com
row 3: supportonline@fabius.com
row 4: myhelp@friday.com
in column G:
row 1: help
row 2: info
row 3: support
I need rows 1, 3 and 4 in column A to be colored.
View 9 Replies
View Related
Jan 20, 2013
My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.
When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.
I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).
for example:
p1
data
data
data
data
p2
data
data
data
data
[Code] .....
needs to become:
p1
data
data
data
data
[Code] .....
I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).
View 5 Replies
View Related
Jun 30, 2006
Where are Macros stored? Are they stored just in the worksheet, or are they stored locally on the machine?
View 3 Replies
View Related
Nov 19, 2009
programming 2 macros in a bank reconciliation sheet I am trying to create.
Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.
The following two situations can occur and which need to be covered with a macro.
Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.
Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.
View 12 Replies
View Related
Oct 3, 2013
MS Query can only hold 16 joins and some other restrictive requirements. Has utilized Stored Procedures to execute a query and have the results returned to Excel? Query in Excel seems very limited.
View 2 Replies
View Related
Feb 12, 2009
I need to call a Stored Procedure in a SQL Server from VB, but do not have the minimun Idea about doing it....Could you help me ?...I made a lot of attemps copiying code from other treads and scratch it, but so far results = none.
Server name: VAIO/SERVIDOR
DataBase: CEDRO
I´m using Excel XP.
View 9 Replies
View Related
Jul 6, 2009
Any way to actually execute a macro stored as a text file?
Without being long winded - I want to create different macro and physically save them as text files. Once saved - I could call them from a list.
View 9 Replies
View Related
Feb 17, 2010
I'm trying to open multiple files, pass them into an array and then work on them but the code below doesn't work (receive a Run-time error '9' message)
Public aFile As Variant
Sub MultipleFiles ()
MsgBox ("Please select multiple book files (holding CTRL as you click on flies) before you click open")
aFile = Application.GetOpenFilename(FileFilter:="Excel (*.xl*), *.xl*", _
Title:="Select required book level files", MultiSelect:=True)
Dim i As Integer
For i = LBound(aFile) to UBound(aFile)
Workbooks(aFile(i)).Activate
'Do something, then repeat with next file in array
Next i
End Sub
View 9 Replies
View Related
