# Summing Concatenated Cells

Dec 7, 2006

I have concatenated the following cells:
A1, B1, C1

The data in each cell is:
1, +, 2

I can concatenate them to show "1+2".

My question is, can I then perform the calculation that the concatenation produces?

I would like to be able to enter different operators into B1 in order to carry out different calculations.

## BOLD And UNDERLINE Concatenated Cells

Apr 23, 2006

I need bolding and underlining cells that are "Concatenated". For example, cell format for cell K12 is already bolded and underlined. If I concatenate (C1,K12,D2) I get a result that doesn't carry over the format of those individual cells - particularly K12 which I want BOLDED and UNDERLINED.

## Searching Concatenated Cells On A Sheet

Oct 27, 2012

Concatenated cells. My issue is how do i search through a sheet with cell values that have been concatenated?

Lets take an example:

I would like to search for a string call sales in sheet1 which would display all the employees and schedules for that department (do note that employees can be part of not just one department). but my cell formatting is as follows:

Sheet 2, Column 1
Employee name

E.g. Row 2 Employee1

Sheet 2, Column 2
Department (cells concatenated)

E.g. Row 2 Sales, Marketing (Concatenated Cell)

Sheet 2, Column 3
Work schedule

E.g. Row 2 8 AM to 5 PM

So when you search for sales, it will display employee1 and work schedule.

## VLOOKUP Or SUMIF With Concatenated Cells

May 23, 2008

I have a worksheet that is utilizing a VLookup to pull some values from another worksheet based on a composite number. I have checked the numbers for their format, length, and value (dangling spaces, etc.). I still cannot get this VLOOKUP to work correctly. I have attached the file.

Column H on the Cost Report sheet holds the formula in question. It concatenates the values in Columns C,D,E,&F to create a composite number to look for in Column B of the Original Estimate sheet. It is supposed to pull the \$ value from Column D of that range.

I have tried everything. I passed the composite number into TEXT, --(Double Unary), INT, tested for both #'s length with LEN and have converted the formatting between text and numbers.

## How To VLOOKUP A Value In A Another Sheet And Return Concatenated Values Of Other Cells

Nov 27, 2013

I need to do a vlookup of the values of Sheet 1 Column A (Ref) in Sheet 2 Column A (Ref).

The results will be the concatenated values of Sheet 2 Column B + C + D + E to be displayed in Sheet 1 Column B (Address).

I'm not sure how I can do this using formula.

I've attached a example.

## Cell Addresses Derived From Concatenated Values In Separate Cells

Apr 2, 2013

I need to return a value from a cell in Column H, but with a row number that varies with each entry.

I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:

=Display Value from in column H: (row number per cell B1)

And cell B1 would include the number 3789.

So the returned result is the value from cell H:3789

## Cell Value Of Closed Workbook With Concatenated File Path In Cells

Sep 24, 2007

I have a report that is collated from several other workbooks. The source workbooks follow the same naming convention throughout the year and so I have managed to concatenate the file path to bring through the text of the filepath and cell reference however cannot work out how to get the value of the cell. For example, I have the file path in A1, file name in B1 and sheet and cell referernce in C1.

I am using {=(A1&B1&C1)} which gives me R:CONFIDENTIALREPORT FOLDER[REPORT 24.09.07.xls]Sheet1!\$D\$1. To make matters slightly more difficult, the source reports would be closed. Is there an easy way or am I going to have to do this in VBA.

## Summing Variable Number Of Cells And Parts Of Cells

Mar 18, 2014

In my financial modelling I often have a calculated number of months of inventory. This number varies. I need to use this number to calculate the actual inventory value. For eg. it may be 3.2 months of inventory; in this case I need to sum 3 cells (current month, current month -1, current month -2) plus 0.2 of the fourth cell (current month -3)

But I may need to change the number of months of inventory to, for example, 4.2. In this case I would need the sum of four cells plus 0.2 of the fifth cell etc etc.

## Summing Up Cells Based On Values Of Other Cells

Oct 25, 2008

I am working on a sheet which calculates payroll. I have a list of people in column A, and in column B, i have their different professionnal status. Their wages vary according to their professionnal status and are in another column C. How can i sum up the wages of only certain people, based on their common professionnal status in column B?

## Summing Up Cells That Have An #n/a

Oct 26, 2008

I am trying to sum up a list of cells that have formulas attached so if there is no number in the formula it shows up as #N/A how do I make it so wherever there is an #N/A it equals 0?

## Summing Cells Where One Has A Formula

Dec 25, 2009

I am attempting to use the if function that looks at a cell to see if it is a certain value and if so it adds the value of the cell above with another cell.

Here is the formula in cell AH26: =IF(G26=Variables!\$F\$4,AH25+AF26,AH25)

the problem is if the G25 did not equal Variables!F4 then AH25 is basically zero or the formula so when cell G26 does equal Variables!F4 then AH26 is supposed to sum AH25 with AF26 but AH25 cell's contents are a formula. How do I get it to ignore the formula and add AF26 with zero instead of the formula?

## Summing Up Blank Cells

Apr 17, 2012

I'm trying to add cells that aren't in a range.

=AA60+AA41+AA22+AA3

Some of those cells are blank and I'm getting an error.

I want excel to treat those blank cells as zeros.

## Summing Cells That Are Multiple Of 15

Mar 26, 2013

I am working on a sum formula for a column and I only want to sum if the value is a multiple of 15 or likewise if the value divided by 15 is an integer. I know that I could use several sumif statements to accomplish this, but there isn't really a maximum value. Is there a simpler way to accomplish this? I am comfortable enough with VBA so that is definitely an option as well.

## Summing Cells That Having Been Filtered VBA

Dec 30, 2006

I'm using the following code to filter a particular range(it works perfectly fine). However I need to SUM Column 'L' once the data has be filtered. and place the result in the LASTS populated cell in Column L. At the moment I am selecting all the data in the column even the data that has been filtered out.

Sheets("Sheet1").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=7, Criteria1:=">16", Operator:=xlAnd

## Summing 2 Cells (repeatedly)

May 16, 2008

I want to sum two cells, doesn't sound that difficult but I want to be able to change one of the cells over time. Hence, if B1 is the total sum cell, and A1 is what I fill in. B1 starts at 0, and if I fill something in in A1, B1 will sum itself and A1 (Unfortunately this gives me a circular reference).
So what I'm looking for is some way to use paste special automatically in the formula B1 I guess.
It is possible and how?

## Summing Blank Cells

Jul 10, 2008

I have the following formula that is not working:

=(K72*0.2)+(N72*0.35)+(O72*0.25)+(P72*0.2)

It displays #VALUE! instead of the weighted sum I want. The reason for this is some of the cells are blank. If cells O72 and P72 are blank, how would I get it to sum just K72 and N72?

Other rows might have different missing cells, so the formula would have to work for different combinations of missing cells, but the weights will stay the same.

## How To Vlookup Summing Similar Cells

Dec 18, 2013

I have attached the file, in the last sheet (Consolidated) i need to get the data from all the previous sheets, my problem is that whenever i have similar cells the data that is showing is only the first value and not the sum of all values,

## Automatically Summing Last 12 Cells In A Column?

Apr 21, 2014

I often look at utility data (start date, end date, use, and cost) and have a large list with up to several years worth of monthly data. I am trying to write a formula that will automatically sum the last 12 entries of "use" no matter the amount of lines; be it 12 or 112.

## Summing Cells Based On Heading Row

Aug 30, 2012

I've got a table with columns for each day of the month, the second row contains the days of the week (MON, TUE, WED, etc...). I want to have two cells at the end of each line, one for summing the numbers in the working days (mon-fri) and another - for the sum of the numbers in the weekend cells.

The idea is the change the content of the second row every month so that the days of the week correspond with the respective date.

what formulas should I employ to accomplish this task and how exactly to do that?

## Summing Cells Containing Numbers And Text

Apr 14, 2013

I have cell A1:A25. each with a number, and the same text "Hour(s)" So cell A1 would be 24 Hour(s) and so forth down to cell A25.

Due to the sheet being large, I can't just insert another column to list the text. I need for cell A26 to sum the numbers ignoring the test in the cells. I searched to forum, but didn't exactly find anything that works correct.

## Summing Cells With Text And Numbers

Apr 28, 2014

I do my indoor cricket team stats and I am having some trouble figuring out what formula to use in order to be able to sum a row of numbers where some cells may contain text and numbers or just text.

Please see the attached spreadsheet to understand what I'm talking about.

In the batting tab, when the batter gets out, they lose 5 runs. Outs are noted by St, R, B or C. In terms of runs, the scorers put wides and noballs to the batter on strike which are noted by W or N and this is worth 2 runs. Sometimes the batter on strike will get runs off a W or N so it's noted down as W+2 (which would give 4 runs) etc. So what I need to be able to do in the batting sheet is sum the total runs and count the number of outs.

In the bowling sheet, it's the same story as the batting tab, but I also need to be able to sum the number of extras given and count the number of wickets taken.

[URL].....

## Conditionally Summing Non-contiguous Cells

Dec 11, 2006

Lets say I have values in A1, A3 and A5 and I want to to build either a sumif or an sum if array formula to conditionally sum them. How do I do this, assuming I don't want to use VBA or named ranges, i.e. create an array of noncontiguous cells and evalute & sum them.

## Summing Non Contigious Cells With Criteria

Jun 21, 2007

I am trying to SUM a range of cells R9:R39, but I only want the cells added that are greater than 0 (zero) and also I don't want the Sub totals included in that sum.

Column R
-507,784
-507,784 Sub

-25,292
-28,700
32,461
-26,632
158,586
0...............................

## Summing/Averaging Last 5 Rows (cells)

Sep 14, 2009

I was trying to sum/average last values in a range. If the range is updated, it should sum/average the last 5.

Tried to do it myself but succeded to do my head in only.

## SUM Formula Not Summing All Referenced Cells

Sep 5, 2006

My problem is that when i used the forumla = SUM(Q11:AW11) to add up the range of cells it dosnt do it. It only added up 2 of the numbers in the range of cells. And missed out everything else. This forumla is being used to add up all the volunteer hours for volunteers for each month and for all the other months the formula worked fine but now its just being a pain.

## Summing A Range With Errored Cells

Oct 12, 2006

I have to work on a sheet where the value in the cells is from a call to the GETPIVOTDATA() function. This may return #REF depending on the data. I need to sum a range of such cells treating a #REF value as zero. In another part of this sheet this is done by {=SUM(IF(ISERROR(D6:D17),0,D6:D17))} but as soon as I go to edit the formula the {} disappear and the formula wont work if I edit it. Putting the {} back in manually doesn't work either. How can I repeat what has been done before. (The sheet was originally created by consultants who have long gone and I have inherited it!)

## Summing Adding Many Time Cells

Jun 15, 2007

I have a spreadsheet with many rows of time totals as the following example: Cell 1 - 08:00 AM Cell 2 - 1:00 PM Cell 3 - 5:00. Cell 3 is just the total, and I have no problem with this aspect... However I have two columns of the above format side by side... and the only way I can sum Cell 3 on both columns is something like the following: =sum(c1+c2+c3+f1+f2+f3...etcetcetc. This can end up in a large formula, and I was just wondering if there was a more efficent way? I tried the following but get a #value! error. =SUM(C1:C6)+(G1:G6)

## Summing Cells To Get Difference (subtracting) Only If Both Are Not Blank?

Oct 19, 2012

I have a spreadsheet of clients' body weights each week which goes in a column and to the right of it I have a "diff" which I want to show weight gain/loss.

So for a client with weigh-ins the week of 10/1, 10/8, and 10/15 the columns will be:

10/1(V1)--Diff(W1)--10/8(X1)--Diff(Y1)--10/15(Z1)...and so on.

If I have weigh-ins for each week, no problem. I would just have to: =SUM(X1-V1) to get the change in weight from the week of 10/1 to 10/8. Say the client weighed 200 on 10/1 (V2) and 195 on 10/8 (X2), the difference (or "diff" (Y2)) would be -5.

But let's say the client didn't weigh-in on 10/1. The difference in weight is going to be 195. Or if they weighed-in on 10/1 but didn't on 10/8 it's going to read -200.

So I have two cells (say V2 & X2) that if one of them is blank, I want the "diff" to read "N/A" or at least leave the cell blank.

## Summing Numbers From Multiple Cells With Text?

May 9, 2014

I am looking for a formula which will sum numbers with less than symbols in front of them (i.e. they are text cells), and then replace the less than symbol again in the summed cell if initially present. The problem is that not every cell contains a < symbol, some of them are just numbers, and not every column contains a < symbol either.

e.g. I am looking for a formula which if entered in row 5 of this example would give these answers.

A
B
C
D

1
<0.001
<0.1
0.2
<0.01

2
<0.0001
0.1
0.2
<0.1

3
<0.0001
<0.2
0.2
<0.1

4
<0.001
<0.1
0.2
<0.01

5
<0.0022
<0.5
0.8
<0.22

## Summing Appropriate Cells If Last Entry Of A Unique Id Is Encountered?

Jul 1, 2014

ID = 1: The B Number is the same for entries of ID =1. Thus where ID = 1 and Last is indicated, Cost = 10.

ID = 2: The B Number changes four times. In cell G20 (Last of all ID =2) Cost = 20+30+40+30 = 120. You are basically summing at each instance the B Number changes.

ID = 3: Cell G24 = 100 +30 (two instances of B Number changing)

ID = 4: B Number is always the same. Cost = 50

ID = 5: B number changes 3 times, Cost = 50+120+140 = 310

In essence, the idea is that if the ID matches for all rows of particular client, the total cost = individual cost. However, if the ID changes multiple times for a single client, the total cost becomes the sum of changed costs, but not the entire column, just summing at each instance it changes. And this summed cost must be entered whenever we see the last instance of a unique ID (This is indicated using Column F).

The problem that I'm running into is that currently I am doing this manually with my actual data set (has nearly 200,000 rows). What excel function or VBA code could I write to automate this entire process?