+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    Registered User
    Join Date
    04-09-2013
    Location
    Gothenburg
    MS-Off Ver
    Excel 2011
    Posts
    21

    Cool 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. #2
    Registered User
    Join Date
    04-09-2013
    Location
    Gothenburg
    MS-Off Ver
    Excel 2011
    Posts
    21

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

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

  3. #3
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  4. #4
    Registered User
    Join Date
    04-09-2013
    Location
    Gothenburg
    MS-Off Ver
    Excel 2011
    Posts
    21

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

    I added the result im after

    Workbook2.xlsx

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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].
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    04-09-2013
    Location
    Gothenburg
    MS-Off Ver
    Excel 2011
    Posts
    21

    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. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    Quote Originally Posted by fabianzetterberg View Post
    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. #8
    Registered User
    Join Date
    04-09-2013
    Location
    Gothenburg
    MS-Off Ver
    Excel 2011
    Posts
    21

    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. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

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

  10. #10
    Registered User
    Join Date
    04-09-2013
    Location
    Gothenburg
    MS-Off Ver
    Excel 2011
    Posts
    21

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

    Workbook3.xlsx

    Ok, Ill 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. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #12
    Registered User
    Join Date
    04-09-2013
    Location
    Gothenburg
    MS-Off Ver
    Excel 2011
    Posts
    21

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

    OMG. So cool!


    Thank you so much


    /Fabian

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

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

    You're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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