# Find text in a column and calculate numbers in the same row

1. ## Find text in a column and calculate numbers in the same row

Hi

New to this forum and i really need some help

I want to find all cells in column 1 containing a specific word and do a calculation with the numbers after the cells.

For example...

A B C D E
1 sol 34 4
2 bord 12 2
3 stol 4 2
4 bord 5 1
5 leka 8 5
6 gata 14 9
7 sol 15 4

I have a list of 50 words that i want to find in column A. Then I want to use the numbers in that row and do a calculation.

"sol" 34 - 4 = 30
"sol" 15 - 4 = 11

D1 = sol
E1 = 41 (adding the results of the calculation from the word "sol"

"bord" 12 - 2 = 10
"bord" 5 - 1 = 4

D2 = bord
E2 = 14

D3 = stol
E3 = 2

D4 = leka
E4 = 3

D5 = gata
E5 = 5

Is this possible?

Thanks

Fabian

2. ## Re: Find text in a column and calculate numbers in the same row

easier to look at the workbook this way...Workbook2.xlsx

3. ## Re: Find text in a column and calculate numbers in the same row

HI Fabian,

Welcome to the forum.

I believe an excel workbook will make this query easy to understand, please upload. Click on "go advanced" button while replying and look for paper clip icon to upload. thanks.

Regards,
DILIPandey
<click on below * if this helps>

4. ## Re: Find text in a column and calculate numbers in the same row

I added the result i´m after

Workbook2.xlsx

5. ## Re: Find text in a column and calculate numbers in the same row

Try this...

Entered in F1 and copied down:

=SUMPRODUCT(--(A\$1:A\$7=E1),B\$1:B\$7-C\$1:C\$7)

In your sample file, there is a space character at the end of the entry in cell E5. Gata[space].

6. ## Re: Find text in a column and calculate numbers in the same row

Cool. It actually works I got the calculation right.

Is there a way to show which word is linked to which result?

7. ## Re: Find text in a column and calculate numbers in the same row

Originally Posted by fabianzetterberg
Is there a way to show which word is linked to which result?
Not sure what you mean.

The results are already linked to the criteria listed in column E.

8. ## Re: Find text in a column and calculate numbers in the same row

Sorry. I got it right now

But...I forgot something. The A column contains other letters than just the word i want to identify from the E column.

A cell in the A column could look like this: /hus/husmodeller/sol

...so if the cell contains "sol" it should count.

Shall i make a workbook to make it easier to explain?

Thanks for helping me!

/Fabian

9. ## Re: Find text in a column and calculate numbers in the same row

Originally Posted by fabianzetterberg
Shall i make a workbook to make it easier to explain?
Yes, that will help.

10. ## Re: Find text in a column and calculate numbers in the same row

Workbook3.xlsx

Ok, I´ll do it with the actual data.

I want to find the cells in column A containing the word in column E and do the calculation. B minus C

For exampel cell E4 is "soderhill" and in the A column you can find "soderhill" in cell A4 and A10. So in the cell F4, next to "soderhill", i want the result of the calculation.

1010(B4) - 73(C4) = 937
837(B10) - 35(C10) = 802
937 + 802 = 1739

F4 = 1739

/Fabian

11. ## Re: Find text in a column and calculate numbers in the same row

Try this...

Entered in F1 and copied down:

=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A\$1:A\$33)),B\$1:B\$33-C\$1:C\$33)

12. ## Re: Find text in a column and calculate numbers in the same row

OMG. So cool!

Thank you so much

/Fabian

13. ## Re: Find text in a column and calculate numbers in the same row

You're welcome!

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1