# 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  Register To Reply

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

easier to look at the workbook this way...Workbook2.xlsx  Register To Reply

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>  Register To Reply

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

I added the result i´m after

Workbook2.xlsx  Register To Reply

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].  Register To Reply

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?  Register To Reply

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.  Register To Reply

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  Register To Reply

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.  Register To Reply

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  Register To Reply

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)  Register To Reply

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

OMG. So cool!

Thank you so much /Fabian  Register To Reply

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

You're welcome!   Register To Reply