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
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
In B17:
Formula:Please Login or Register to view this content.
Then copy to the right and down.
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.
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.
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
Dear Glen good morning,
Please see image and updated sheet.Attachment 706091
But the important thing is that the room types are NOT NECESSARILY in the same order in both of the pink-coloured blocks. Right??
Dear Glenn,
Exactly..
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)),"")
I randomised the order of the rooms in the upper block.
b17:
With room types are random in pink and white colour rangePlease Login or Register to view this content.
Quang PT
Thank you Very match Glenn, that worked perfectly
You're welcome. And it must be OK, as Bebo has come along with the same formula at the same time!!
Dear bebo021999,
Thank you for your reply
Yes, is what i was looking
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks