1

With my sheet, I need it so that if the job is any type of "Repair" then it minuses 1000 from the business profit but if it's anything else it doesn't minus the 1000 from the business profit. I have spent a bit trying to work this out however I am completely unsure on how to do this.

The current formula I use is:

=IF(J4=TRUE,SUM(F4-I4)-1000,"")

which makes sure it won't appear until the paid is done and also removes the $1000 from the price however when I choose something like "Tow" or "Lock Pick" it will still remove the $1000.

A few things I have tried are

=IFS(QUERY(E4, "Where E contains 'Repair'")<>"",F4-I4-1000,E4="Tow",F4-I4)

I am rather new to all these sheets so formatting will be wrong which is why I believe the above didn't work.

=IF(E6=INDEX(Key!$D$1:$D$3,MATCH(E6,Key!$D$1:$D$3,0),0),F6-I6-1000,F6-I6)

I have another page with "Key" on it which is used to grab the names and prices for everything and matching it. I thought that with the above formula that if I can get it to match the repair types then it would output the -1000 for that else it wouldn't minus anything from it. I am not sure if the formatting is wrong however it seemed fine to me.

Photo for reference, I can explain anything needed.

enter image description here

  • can you share a copy of your sheet? – user0 Jul 27 at 10:49
0
  • delete everything in H4:H range
  • paste this into H4 cell:

    =ARRAYFORMULA(IF(J4:J=TRUE, 
     IF(REGEXMATCH(LOWER(E4:E), "repair"), F4:F-I4:I-1000, F4:F-I4:I), ))

    0

  • 1
    You're a life saver, thank you very much. These spreadsheets are all new to me so your help is very much appreciated. Also sorry for not sharing a copy got caught up with some things so I hadn't read this page. – Melk Jul 27 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.