SUMIF With Concatenate Returning Zero
Feb 13, 2012
I have two sheets in my workbook, in sheet two I have a concatenate to merge 3 columns [Sheet 2 cells C3,D3,E3]. An example of the result is:
X.MB.BQW.PM.99.01730149186SF50
I am then trying to run a SUMIF on this column from sheet 1 to look up and add data from another cell in. The formula I am using is:
=SUMIF(REM_HOURS!$A$3:$A$1902,AI6,REM_HOURS!$J$3:$J$1902)
where REM_HOURS is sheet 2 and AI6 is a concatenate in sheet 1 which provides an equal value to cells in REM_HOURS column A.
All that is being returned by my formula is 0. I'm using Excel 97 on a Windows XP machine.
View 5 Replies
ADVERTISEMENT
Dec 20, 2011
I have this code
Code:
Selection.FormulaR1C1Local = "=INDIRECT(CONCATENATE(""["",R4C26,""]Sheet1!"",RC))"
Which creates this in the excel cell:
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!",D4))
But for some reason, it returns a 0. However, when I change the code in the excel cell to this
=INDIRECT(CONCATENATE("[",$Z$4,"]Sheet1!D4"))
It works. But I need to be able to change D4 in the macro, so doing it that way won't work for me.
View 7 Replies
View Related
Feb 15, 2010
Im tryng to do a SUMIF formula that has several criteria as answer but using the concatenate formula, but it doesnt seem to come back with the answer - im using AND/OR formula aswell, but have a feeling im doing it wrong.
It does work when i use the single concatenate, but not when i try a do more than one
View 4 Replies
View Related
Mar 2, 2014
I am trying to sum the data in "details sheet" column H into "monthly sheet" based on the period and type match.
I am not sure where I am going wrong as the column references returned values that they should. However, the values in Col H from details sheet are not being summed up based on the criteria into monthly sheet.
I am getting zero amounts.
View 5 Replies
View Related
Oct 9, 2009
My SUMIF function only works when the workbook that I'm referencing to is open. When it's closed and I open the file it shows #VALUE!. How do I fix this problem?
View 2 Replies
View Related
Jun 22, 2008
I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.
I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…
A = 2000 text
B = 1234 text
F = 2000 trailing spaces, text
G = 1234 trailing spaces, text
I = 10,000
Answer in column J
I want 20001234 = 20001234 = 10,000 for the whole column.
I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.
View 9 Replies
View Related
Oct 6, 2007
I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.
ColA ColB__ColC
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
View 5 Replies
View Related
Aug 11, 2013
Sampling table :
one
two
three
four
one
two
three
one
two
one
Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")
one , two , three , four
one , two , three
one , two
one
Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?
My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,
View 9 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Sep 17, 2009
I need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
View 6 Replies
View Related
Apr 21, 2009
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
View 5 Replies
View Related
Feb 27, 2012
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
View 2 Replies
View Related
Sep 29, 2006
I have 2,000+ cells containing text that I need to break out into multiple cells. They are names (ex. John M Smith MD) and I need each part of the name in it's own column. I need the opposite of concatenate.
View 3 Replies
View Related
Dec 11, 2008
I'm having a rather difficult time getting this one.
I am concatenating (is that a verb?) a bunch of cells to create an item code.
The combination of two different cells need to be able to give different values so I can dump the appropriate value into the CONCATENATE formula.
A picture is worth a thousand..
View 4 Replies
View Related
Jun 19, 2009
I am trying to get 2 rows in one drop down without having to create another combined row. im not sure what formula should i use. I have attached an example.
View 7 Replies
View Related
Sep 2, 2009
I want to write formula in cells(many cells!) using concatenate. The desired output:
In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6
in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7
in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F
Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.
View 2 Replies
View Related
Jan 2, 2013
I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:
1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8
I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008
Easy enough! Now adding the following is the problem:
1. Cell A2= 12345 Cell B2= 67 Cell C2= 89
I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089
Now D2 has one too many characters from C2
I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.
Is there a way to use a variable with the concatenate or am I using the wrong formula period ??
View 3 Replies
View Related
Jul 9, 2009
A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".
View 7 Replies
View Related
Jul 25, 2009
I'm trying to use a macro to write a formula within a column of data.
Here's the
View 3 Replies
View Related
Feb 2, 2010
i trying to merge column A1 (ABC) with column B1(XYZ), B2(LOL), B3(ROF) ..etc
with a simple =CONCATENATE(A1,"-",B1) , i can get "ABC-XYZ" , but when come to column B2 , i only can get "-LOL" , any solution ?
View 4 Replies
View Related
Nov 2, 2006
Create a column of concatenated values to serve as a "primary key" for a compare and merge tool (Synkronizer). For some reason, the CONCATENATE function isn't being recognized.
If I enter
=CONCATENATE(A2,B2)
instead of showing the strings located in A2 and B2 it displays
=CONCATENATE(A2,B2)
in the cell.
I tried creating a new spreadsheet and it works just fine. Does anyone have an idea of what might be happening? I have compared all of the options between the two spreadsheets and don't see any difference in them.
View 9 Replies
View Related
Nov 14, 2008
I have 6 columns:
A1 = LastName
B1 = FirstName
C1 = LastName2
D1 = FirstName2
I need to concatenate into 1 cell so it looks like this: FirstName LastName;FirstName2 Last Name2. Now the challenge is that there are many instances where C1 and D1 are blank (don't have values).
So here was my attempt at a formula:
=IF(A1>""&B1>"",CONCATENATE(B1," ",A1))& IF(C1>""&D1>"",CONCATENATE(";"&D1,"",C1),"")
Now this works, but it ALWAYS leaves a semi colon at the end (even when there is no C1 and D1 to concatentate. Thus I put in my IF statement the "".
View 4 Replies
View Related
Jan 23, 2010
What function can get me the first letter from the first name and the whole last name together and then add @email.com. Example: Hanry Jones = HJones@email.com
View 2 Replies
View Related
May 16, 2012
I have the below set of data for example,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
I need the output as,
aaabbbcccdddeeeaaabbbaaabbbcccaaa
As we know, a simple concatenate will work. But my columns are not limited and not easily countable. In my real scenario for some of the rows, data exist from column "A" till column "IB". So, I need to first identify the column data exist in each row and then to implement the concatenate function.
View 3 Replies
View Related
Jul 24, 2012
Currently I am trying to concatenate two arrays stockotherarray and stockfittingsarray to create stockarraynew
Below is my code, I keep receiving a compile error.
Sub stockarraynew()
stockarraynew() = Split(Join(stockotherarray & Join(stockfittingsarray))
End Function
View 9 Replies
View Related
Jul 30, 2012
I'm trying to take the contents of 3 columns- A, B & C and have them form what I can use for a search string in column
D that I can use in a database I have.
Column A is first name
Column B is last name
Column C is Company Name
This is what i'm trying to get it to look like in column d:
("First Name Last Name" OR "FirstName.LastName") AND @companyname.com
View 6 Replies
View Related
Apr 11, 2013
On tab 2 of a spreadsheet, I am trying to concatenate several cells on tab one! Everything comes together in the cell on tab 2, however it does not hold the date format. It turns it into general text so just random numbers that do not even tie to the date. Any way to lock the date format for this idea?
View 7 Replies
View Related
Mar 3, 2007
I have a list containing 3 the abbreviations of states in A1:A3 (one in each cell), and I would like to concatenate the list in cell B1 to display the list like this: "CA, TX, FL". At times, the list will only have one state (always in A1), but at other times it may have 2 or 3.
I tried: =IF(ISTEXT(A),A1&", "&A2&", "&A3,A1)
But, if A2 does not list a state then it returns "#value!"; and if A2 lists a state but A3 does not then it returns only the value in A1.
What can I do to get it to list all states listed in column A whether there are 1, 2, or 3 states listed?
View 9 Replies
View Related
Apr 27, 2007
I have 7 consecutive cells which contain a %
e.g. A1 120%, B1 35% etc
I am trying to put them into one cell using concatenate
but am getting 1.20.3529411764705880.705882352941177
I would also like to separate them by comma,
View 9 Replies
View Related
Sep 26, 2007
I am using the concatenate function, however my date field, instead of maintaining its 08/07/07 state, changes to the annoying 5 digit 33935 number.
Even if I cut and paste it as special / values, it stays as the 5 digit number. Can this be avoided somehow?
View 9 Replies
View Related
