Sum With Multiple Criteria Without Having To Concatenate
Mar 13, 2008
I have data in sheet 1 and sheet 2. Sheet 1 contains a product code and line number and sheet 2 has the corresponding cost.
I want to be able to bring back the cost to sheet 1 without having to concatenate the product and line number.
I'm sure this is possible with a formula, but I am currently stumped.
I pasted the sheet 1 and sheet 2 data below. Note that the sheet 2 data extends the full row and does not wrap.....
View 9 Replies
ADVERTISEMENT
Aug 31, 2012
I have a worksheet entitled 'Data'. In this worksheet there is a table consisting of 4 columns plus relevant data:
TABLE 1:
Project
Benefit Type
Delivered or Enabled
Benefit
PJ1
Financial
Delivered
Saving of $4M over 24 months.
[code]....
I have been trying to create a formula that will enable me to pull data from the 'benefit' column(column D) so that the cell contents populate in a single cell in a table in a different worksheet.
TABLE 2:
Financial - Delivered
Financial - Enabled
Tech - Delivered
Tech - Enabled
Green - Delivered
Green - Enabled
[code]....
So, as an example, I am hoping that a formula can be created which pulls the text from relevant cells in column D when criteria from columns A, B and C are met e.g. Tech benefits that are Delivered in PJ2 would populate cell E3 ('Tech -Enabled') in Table 2 with:
Continued maintenance of hardware.
Increased capacity.
View 1 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
Dec 20, 2012
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID
VOL
001
01
001
05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique)
VOL (concatenated)
001
V01, V03, V05
002
V01, V04
003
V06
PS: I have people using this table with office 2003, so compatibility is necessary...
View 1 Replies
View Related
May 3, 2007
I created a concatenate on a co-worker's spreadsheet so I could utilize the data, and it works fine. But then I realized when reviewing the results, that there are MANY instances where not all of the fields that the concatenation pulls from contain data. I verified the source, and found out that any of these fields may or may not contain data. Now I need to account for this within my formula.
The cells being concatenated are in columns G-K, my first formula (in cell L4) is:
=IF($A4=3,CONCATENATE(G4,"; ",H4,"; ",I4,"; ",J4,"; ",K4),"")
Now I am using a "; " to separate between each cell's text. I need to adjust this formula to account for the fact that any of those cells MAY not contain data. And if it does not, I do not want the separator ("; " to show), because otherwise, I get entries that display like this:
up to 10.; ; 1.NS.1.1
If necessary, I can easily place my separator in a specific cell (M1 would be good) and access it from there rather than the way I initially wrote the formula.
View 9 Replies
View Related
Jan 18, 2013
I looking for an option to concatenate various cells but only when another criteria is complied, also to sum other value. I.E. concatenate cell "A" when the value on cell "B" is repeated, also sum the value on cell C.
A1=D1 B1=num 1 C1=1
A2=D2 B2=num 1 C2=1
A3=D3 B3=num 1 C3=1
A4=D4 B4=num 2 C4=1
A5=D5 B5=num 2 C5=1
Waited result, assuming formula on the next row:
A6=D1,D2,D3 B6=Num 1 C=3
View 1 Replies
View Related
Jul 22, 2014
I am looking to write a formula that can concatenate cell A$1$ (absolute) & B1: B245 (dynamic)and then A$2$- B1:B245 and then so on.
So for example, I need to concatenate cell A$1$ & B1, and then A$1$ & B2, all the way up until B245, and then I need to restart the sequence with A2 and B1.
Formula to preform this concatenation, or will this need to be done with VBA?
View 1 Replies
View Related
Jul 17, 2008
The formula below works great for counting. I need to use the same formula to CONCATENATE all of the names in B:B. It seems as though I should just replace COUNTIFS with CONCATENATE and change Data!C:C to Data!B:B, but it doesn't work. I tried a search on the web using the title of this thread but came up with very few clues. Is it even possible to CONCATENATE with multiples IFS?
=COUNTIFS(Data!C:C,IF(B2="All","*",B2),Data!D:D,IF(B3="All","="&W5,Data!E:E,"
View 9 Replies
View Related
Aug 9, 2009
I have multiple rows that I wanted to concatenate. I want the values delimited to a symbol like +. The problem using the sample formula =CONCATENATE(A3," = ",B3,"+",C3,"+",D3) is if there's a blank row the + sign will still display. I attached sample file with the desired result.
View 5 Replies
View Related
Nov 25, 2009
I have a macro to concatenate multiple cells but unfortunately it is not giving the result,
It has to concatenate below mentioned cells and put the result in column N
Sub ConcatColumns()
Do While ActiveCell "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
Range("N1").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-10],RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-1])& ActiveCell.Offset(0, 0)"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
View 9 Replies
View Related
Apr 19, 2007
I have a formula, and it works okay, but I have to change it based on how many characters appear in a cell. I would like to avoid this, as there is just too much room for error.
The end result of my formula is to get a result like the following: U1C01EP or U1C01L04EP depending on which columns are completed. But the "variation" on the number of characters in the cell (1 or 2) and accounting for the leading zero is what is getting me confused.
In cell R4, I have the following formula:
=IF(S4="","",IF(X4="",CONCATENATE("U",V4,"C0",W4,$R$1),CONCATENATE("U",V4,"C0",W4,"L0",X4,$R$1)))
Column V always contains only 1 character, so this is not an issue. "U" will be added before the contents of this column in the formula
Column W will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "C" and then the contents of column W.
Column X will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "L" and then the contents of column X. Now this column may also be blank, if that happens, then there should be no "L" within the result, that portion would be left off.
Cell R1 contains the code that is added to the very end of my result. This code will change, so it is placed in a fixed location to be added to the end.
And I would like to keep the IF statement that I wrote accounting for Column S being Blank, yielding a blank cell completely as a result.
View 6 Replies
View Related
Jun 21, 2008
I've searched quite a few times for Concatenating Multiple Columns and ones for Concatenating with Spaces.
I've found solutions that say use
=concatenate(A1, " ", B1)
or the VBA equivalent
I have at least 15-20 columns which I wish to concatenate.
Is there an easier way than to type for all 15 columns?
=concatenate(A1," ", B1," ", C1,....)
Also, the number of columns of each row vary
View 9 Replies
View Related
May 8, 2014
Say in L4 on form test i want to look for the value of A4 in Drill data range a2 to z1000 and retun a concatenated string of something like "sav 407 : 08-may to 11-may"
View 3 Replies
View Related
Jul 30, 2014
I've made a "Budget Calendar" so to speak.., with my bills on the first sheet, and the Months in the following sheets. What I'm trying to do is say, for instance, the day is the 11th on the calendar, (there are two bills due on this day), and I have the bill scheduled to be on the 11th, can I use a formula to return what bills are due?
On the "Bill Dates" sheet, you can see where I'm kind of going with this...
On the August calendar, under the 11th, I want it to say "Internet, gas (utility)" in cell D20, or "Internet" and "gas (utility)" in D20&21, respectively.
I can get E20 to return the total bills due for the day using "=VLOOKUP("11th",'Bill Dates'!$B$2:$D$395,3,FALSE)", and it'd be awesome if I could get it to return them in different rows as well.
Finance Calendar.xlsx
View 3 Replies
View Related
Mar 4, 2014
I have 2 worksheets, Worksheet 1 has Customer Magic Number on it as a reference and a few customer details and Worksheet 2 has Customer magic number and contact fields.
I am currently using the formula
=INDEX($C$3:$C$575, SMALL(IF(N2=$D$3:$D$575, ROW($D$3:$D$575)-MIN(ROW($D$3:$D$575))+1, ""), COLUMN(A1)))
to show the contact codes in sheet 1 however I also need to show the Notes which are located in Columns G:I, Is there an easy to use the index & match functions as above with the concatenate function to add the notes in the cell beside where I am inputting the contact codes?
View 2 Replies
View Related
Aug 20, 2013
I am trying to concatenate multiple strings of text where each may have composite or compound formatting. Is there any way to maintain the formatting? The Characters.Insert method won't work because the resulting string is greater than 255 characters. See an example below.
String 1: When you simply concatenate
String 2: these strings, Excel discards
String 3: formatting at the character level.
Required Result: When you simply concatenate these strings, Excel discards formatting at the character level.
View 5 Replies
View Related
May 14, 2008
I think this is an easy one, but i can't figure it out. I have a spreadsheeet with multiple concatenate functions programed in. All of them work. If, however, i try to change the formula inside the concatenate function it freezes and will not calculate the new cell. I have the calculation tab set to automatic in the options tab...
View 9 Replies
View Related
Jan 21, 2009
If I have the following data set in cells A1:C9:
IDCommentFirst Unique
109876Low OilTRUE
109876Checked On 12/12FALSE
109877Checked on 12/15TRUE
109878Correct LevelTRUE
109877Correct LevelFALSE
109878PerfectFALSE
109877NewFALSE
109878Correct LevelFALSE
In the First Unique column (column C) I have formulas. In C2 I have this formula (and then I copied it down):
=COUNTIF(A$2:A2,A2)=1
In cell E2, I have this formula to count unique occurrences:
=SUMPRODUCT(C2:C9*1)
In cell G2 I have this formula to extract unique records (entered with Ctrl + Shift + Enter):
=IF(ROWS(G$2:G2)
View 9 Replies
View Related
Mar 31, 2009
I am trying to find 3 values (X,Y,Z) in row A and concatenate those three values in column A for the entire range below, say A2:A100.
View 9 Replies
View Related
Jun 26, 2008
I have a text document disaggregated into sentences each in different cells. I need a way of selecting different cells with a mouse, and through a Macro, have the text from those cells viewed in concatenated form into new textbox generated by the Macro.
View 9 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
May 23, 2014
I have several lists of hundreds of emails that I need to list in an email string via Lotus Notes. I need a space and comma in between each name, ex:
john.doe@dm.com, john.doe1@dm.com, john.doe2@dm.com
I am using the below formula manually but it is taking too long. How to automate this? Is there a way to create the space and comma for as long as the list is?
=CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B12,", ",B13,", ",B14,", ",B15,", ",B16,", ",B17)
View 2 Replies
View Related
Mar 4, 2012
What is the best way to concatenate with a semi-colon across multiple cells in excel. The one kicker is I only want to concatenate where the cell is populated.
If I write a simple Concatenate or combined columns, if a cell is blank I get the multiple.
View 1 Replies
View Related
Aug 13, 2012
I wish to Concatenate contents of several columns into a separate column, i tried using the following "column1 & column2 & column3 etc..." however with this the blank cells get concatenated too...What should is use so that contents get concatenated but blank cells are ignored?
View 4 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 16, 2013
I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.
Below is an example with the solution I am attempting to produce.
Sample Table
Name
Dept
Abbruzzese,James L
188100
[Code] ......
Output
Name
Dept
Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708
Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435
View 2 Replies
View Related
Dec 21, 2007
I have one row per day of the month in column A. each row has one or more cell entries in column B .... to column (variable) Right now I use a formula =concatenate(B2,CHAR(10),C2,CHAR(10),D2.....) for as long as it has to be, but this has its limits and requires a lot of editing as the number of filled cells for each row vary.
would it be possible with some macro code to manage the variable number of cells and then insert verything, wrapped text, in a single cell (can be cell Bx or a cell in a new column added next to the dates column) As soon as everything is in the single cell, the original data cells can be emtied. I can record a macro but that does not allow me to manage the variable number of cells . a small sample file attached.
View 7 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
Jun 10, 2014
The 3 vlookup will be in a single cell for concatenation like :
=vlookup1&"-"&vlookup2&"-"&vlookup3
Problem the are 3 vlookups where it will return names (with format -> Fname, Given Name Middle) and probably blank returns.
I have problem with the replace since there are also spaces between the Fname,Gname and Mname sample name with spaces.
View 6 Replies
View Related
Mar 24, 2014
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies
View Related
