+ Reply to Thread
Results 1 to 15 of 15

Subtract if text match

  1. #1
    Forum Contributor
    Join Date
    03-19-2020
    Location
    Greece
    MS-Off Ver
    office 365
    Posts
    123

    Subtract if text match

    Good evening to all,

    I need to subtract value in cell B17 if text match.
    If text in cell A3 match with text in range AJ3:AJ13, then subtract AI3-B3.
    I need to do the same for the following cells A4,A5 etc.
    Thank you in advance for your help
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Subtract if text match

    In B17:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then copy to the right and down.
    Attached Files Attached Files

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Subtract if text match

    I need to subtract value in cell B17 if text match.
    If text in cell A3 match with text in range AJ3:AJ13, then subtract AI3-B3.

    You say you need to subtract B17 BUT then say AI3-B3
    where is the result put
    you can use a countif() to see if A3 matches the range $AJ$3:$AJ$13
    so
    COUNTIF($AJ$3:$AJ$13, $A3)>0
    with an IF
    IF(COUNTIF($AJ$3:$AJ$13, $A3)>0, Subtraction if true , If FALSE do WHAT )
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Contributor
    Join Date
    03-19-2020
    Location
    Greece
    MS-Off Ver
    office 365
    Posts
    123

    Re: Subtract if text match

    thank you both for the answers.
    The result is correct, the only thing is that range in A3:A13 can be randomly as its coming with an if formula from another worksheet.
    That mean, if A3 is Panoramic double sea view, to recognize that the total rooms for the subtract are 13, if A3 is Deluxe sea view Sharing pool to recognize that the total rooms for the subtract are 5.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Subtract if text match

    Please amend your sheet to show a few manually calculated answers IN THE LOCATION where you expect to see them.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  6. #6
    Forum Contributor
    Join Date
    03-19-2020
    Location
    Greece
    MS-Off Ver
    office 365
    Posts
    123

    Re: Subtract if text match

    Dear Glen good morning,
    Please see image and updated sheet.Attachment 706091
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Subtract if text match

    But the important thing is that the room types are NOT NECESSARILY in the same order in both of the pink-coloured blocks. Right??

  8. #8
    Forum Contributor
    Join Date
    03-19-2020
    Location
    Greece
    MS-Off Ver
    office 365
    Posts
    123

    Re: Subtract if text match

    Dear Glenn,

    Exactly..

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Subtract if text match

    In A17, copied across and down:

    =IFERROR(INDEX($AI$3:$AI$13,MATCH($A17,$AJ$3:$AJ$13,0))-INDEX(B$3:B$13,MATCH($A17,$A$3:$A$13,0)),"")
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Subtract if text match

    I randomised the order of the rooms in the upper block.

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Subtract if text match

    b17:
    Please Login or Register  to view this content.
    With room types are random in pink and white colour range
    Attached Files Attached Files
    Quang PT

  12. #12
    Forum Contributor
    Join Date
    03-19-2020
    Location
    Greece
    MS-Off Ver
    office 365
    Posts
    123

    Re: Subtract if text match

    Thank you Very match Glenn, that worked perfectly

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Subtract if text match

    You're welcome. And it must be OK, as Bebo has come along with the same formula at the same time!!

  14. #14
    Forum Contributor
    Join Date
    03-19-2020
    Location
    Greece
    MS-Off Ver
    office 365
    Posts
    123

    Re: Subtract if text match

    Dear bebo021999,

    Thank you for your reply

  15. #15
    Forum Contributor
    Join Date
    03-19-2020
    Location
    Greece
    MS-Off Ver
    office 365
    Posts
    123

    Re: Subtract if text match

    Yes, is what i was looking

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to subtract date if values match?
    By AccountingJ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-11-2019, 10:18 AM
  2. IF Color and Text Match then Subtract
    By Quivolt in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-27-2017, 03:26 PM
  3. subtract data using match and vlook
    By mazan2010 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-25-2017, 05:07 PM
  4. Cell calculation - Text query ( Add / Subtract depending on text )
    By Glorioso999 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-14-2016, 10:33 AM
  5. Need help with VLOOKUP or INDEX/MATCH with Subtract formula
    By drheenas in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-05-2014, 07:14 PM
  6. need to compare match the data of each column and then subtract it
    By yageet in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2010, 01:31 PM
  7. Find match and subtract pair
    By Canuck 'Eh in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-15-2005, 07:17 PM

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