## a formula to search for names in columns and then add the figures

MS EXCEL

### Next

• 1. combine columns
I have two columns that I'd like to combine into a single column. For example, I have two colums that similar to this: A 4 B 5 C 6 I'd like to make these two individual columns become A4 B5 C6 Thanks
• 2. using links to show data
I am trying to find a way of showing maximum data in minimum space. I want five links that will import a range of cells from other worksheets when selected. I don't want to transport to the page where the data is stored but have that data imported to a range. Whether it be in form or whatever. Is this possible
• 3. Sumproduct with offset?
Hello everyone, I have a large data where I need to sum certain values based on the criteria various rows above or below. For example, I need to sum the values of C one row down the criteria, A=1 and B=1 (sum of 6 and 5 in column C). A B C 2 3 2 1 1 3 4 3 6 1 1 4 2 5 5 I have tried to combine sumproduct with the offset function, but I just can't get it to work. Thanks. -matt
• 4. I need to find the Average from Column A - but Reference Column B
Column A has a Range of Dollar Values (\$0 - \$100,000). Column B has a Range of numbers from 0 to 100. I want to return an average of the Dollar Values in column A but only using the Dollar Values that have a corresponding number in column B between the range of 0 to 10.
• 5. Calculatng bowling averages
Howdy, Cells a1, a2, a3 contain bowling scores. Cell a4 is the total of a1:a3 Cell a5 is the total in a4 divided by 3 (total games bowled so far) equaling the bowler's average that week. Cells b1, b2, b3 contain the next week's bowling scores. Cell b4 is the total of b1:b3 NOW, in cell b5, I want the the total pins felled from cell a4 PLUS the total pins felled in cell b4 divided by 6 (total games bowled so far). I know how to set THIS up, what I don't know is how to extend this formula easily for the next 28 weeks! When I drag the formula, it no longer starts with the data in a4. It jumps ahead by one column each week. I have to manually change the range of cells I want to add together then change the number that is divided into that range. Here is a sample starting in cell a4. I entered this manually. It's the formula in row 5 that is giving me the fits. How can this be done with dragging the cells or entering a different formula? Thanks....(I'm outta breath now....) Michael =SUM(A1:A3) =SUM(B1:B3) =SUM(C1:C3) =SUM(D1:D3) =SUM(E1:E3) =SUM(A4)/3 =SUM(A4:B4)/6 =SUM(A4:C4)/9 =SUM(A4:D4)/12 =SUM(A4:E4)/15

### a formula to search for names in columns and then add the figures

```I need a formula to search for names in columns and then add the figures that
are in a different column, so for example i have names in one column and then
need to find each name and add the dollar amounts that are listed in another
column...
```

### RE: a formula to search for names in columns and then add the figures

```Easiest way is with SUMIF().  See Excel Help for it for the details.
Basically, identify the cells with names as the first parameter, a cell where
you will enter one of the names (or an actual name), as the second parameter
and the cells with the numbers to be added as the 3rd parameter.

Assume list of names in cells A1, A2, A3, A4, A5 and A6 with some associated
numbers in column B on the same rows, and that you can enter any one of the
names into C1.  This formula would give you total of numbers for the name
entered in C1:
=SUMIF(A1:A6,C1,B1:B6)

```

### RE: a formula to search for names in columns and then add the figures

```You can do this using PIVOT Table.

In Excel 2007
Insert-> Pivot Table
Select the cells containing your data,

click on name and dollar amount fields (first row should contain the lables,
say Name & Amount.
Move Amount to Sum (Sigma symbol) field and in the drop down change field
property to Sum

```

### RE: a formula to search for names in columns and then add the figures

```Thank you, I am going over this site and it is great, I didnt expect to get

```

```Glad we could help you.

JLatham's solution is easier and more elegant.
You do need to learn about PIVOT tables... it will save you lot of grief.
Try to master VLOOKUP also.

"Vercingetorix.I" wrote:

> Thank you, I am going over this site and it is great, I didnt expect to get
> an answer so quickly either...
>
> "Vercingetorix.I" wrote:
>
> > I need a formula to search for names in columns and then add the figures that
> > are in a different column, so for example i have names in one column and then
> > need to find each name and add the dollar amounts that are listed in another
> > column...
```

```Hello,

I'm using a For Next loop to create some named formulas.

*****************************************************************
For LoopCounter = 11 To 18
RefersTo:=Service.Cells(LoopCounter, 2).Value
Next LoopCounter
*****************************************************************

the RefersTo arguments looks like this:
"ABS(C\$173-SUM(C\$158,C\$165,C\$168))<1" (columns relative)

When I run my procedure having cell pointer on column"C" I get
named formulas, which looks like this:
"ABS(E\$173-SUM(E\$158,E\$165,E\$168))<1" (offset of two rows).

When I use column"A" in the RefersTo argument I get column "C" in my
named formulas.

CRAZY!

Any help is appreciated.
Excel XP SP3
WIN XP SP1

Best Regards
Werner
```

```Hello,

I've been searching all day to try to find an answer to my question.
Unfortuneately I haven't been able to find anything specific to my
issu.  This may be because I am not using the right keywords, so
forgive me if this is a duplicate question of another posting.

Anyway, I have an excel spreadsheet of data that is sent to my group
each month.  I am trying to automate the process of extracting the
data/columns that we need to import into access.  2 of the columns
contain fullnames.  I need to split these columns into LastName and
FirstName which I have already done.  However, in my code it the column
is chosen by the column range i.e (Q:Q).  However, this is not always
true every month, so I wanted to be able to pick the column by the
column header instead.  I am using the code below, but for some reason
it isn't moving across the column.  It is moving down the rows.  Can
someone please take a look at it and tell me what I am missing?

Thanks

SearchValue = "GOwner" ' Set search value"

Set rng =
Worksheets("Sheet2").Range("A1").SpecialCells(xlCellTypeLastCell)
'lLastRow = rng.Row
lLastCol = rng.End(xlToRight).Column

Dim cVal As String
For i = 1 To lLastCol
MsgBox "Column = " & i
If Cells(i, "A").Value = SearchValue Then
MsgBox "Search value found at Column: " & i
End If
Next i

```

```i have a list of figures thats already generated from another report . i look
at it and code the red ones i want to pay , the blue ones i will pay next
week and so on . the report has about 6 months across the top so it needs to
be kept neat by not really adding colums in beside each months column

"City Girl via OfficeKB.com" wrote:

> Will there be a formula / criteria to determine which ones will be in blue
> and which ones in red or are you just going to randomly choose figures for
> colour?
>
> karen wrote:
> >i have a column of fifures . some i want in red some in blue . i then want a
> >totall at the bottom of the red figures and blue figures . Is it easy to do?
>
```