if cell contains chosen text, add value from another cell in same row

**desybabe** · 04-06-2009, 01:52 PM

Hi
I have a sheet with 65,000 rows.
Column c contains text and numbers (phrases from search engine results seraches).
Column G contains the number of times the phrase was searched.

I am wanting to run a formula / macro which goes like;

If cell in column c contains variable then copy cell g out of same row to cell h. This way I can quickly sum up column h to see total number searches by variable

I have looked through forum for past half hour and could not find what i needed.

Thanks in advance anyone.

**NBVC** · 04-06-2009, 01:56 PM

Formula in H1:

=IF(ISNUMBER(SEARCH("variable",C1)),G1,"")

replace "variable" with your keyword

**desybabe** · 04-06-2009, 02:10 PM

ace, works a treat.
thanks for immediate response.

quickest ever response to a problem i have ever had.

one user question.

how do I paste it down to the bottom of the column without having to wait for ages whilst I drag it down?

i have 60,000 rows and it is taking for ever?

ctrl shift end highlights all the other columns

any ideas?

**NBVC** · 04-06-2009, 02:14 PM

Copy the cell with the formula in it and then go to next cell.

Do a CTRL+G and enter the cell reference you want to end at in the Reference field.

Hold the Shift Key down and click Ok.

Now Paste.

**JasonHopkins** · 10-03-2011, 01:12 PM

Basically, I have a list of data listing spend for different financial year. There are around 4,000 lines set out as per below (example of one supplier) :

Supplier No Supplier Year Total Net No
aae327 A A Engineering & Hydraulics Ltd 2010 £420.00 267
aae327 A A Engineering & Hydraulics Ltd 2011 £455.20 347
aae327 A A Engineering & Hydraulics Ltd 2012 £302.22 137

As you can see they are set out over three lines for each financial year. It would be great if I could have just one line with results set out as below:

Supplier No Supplier Name 2010 2011 2012
aae327 A A Engineering & Hydraulics Ltd £420.00 £455.20 £302.22
dfsdf Abp Marine Enviroment Research £0.00 £0.00 £302.22
accor123 Accor £0.00 £507.65 £3,022.22

I really have no idea how to get around this so any imput would be brilliant. I have attached a rough example of the sheet.

Many thanks
Jason

**NBVC** · 10-03-2011, 01:18 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

**JasonHopkins** · 10-03-2011, 02:22 PM

Apologies, posted by mistake in wrong window. Have posted as new thread in general help! Wern't sure how to delete.

Thanks
Jason

**jrut5422** · 04-01-2012, 10:42 PM

building on the origional post...

if a cell in column C conains "variable", how can I add all the fixed amounts of cell G of the same row for the entire column.

In other words, everytime O appears in column C, I want to know the sum of the fixed numbers in the same row for the entire column G each time 0 appears in column C

C G
0 2
2 2
2 2
0 1

I'd need the cell to be 3 (2+1 from G2 and G5)

I have either made this way too hard or way too easy...help!!

**Bonnie2222** · 04-01-2012, 11:55 PM

How did you attached the file, I have some problem to do that, could you help?

**protonLeah** · 04-01-2012, 11:58 PM

@jrut5422
@Bonnie2222
Please read and adhere to these simple rules!

#2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.