Stumped on Excel Formula and Grateful for Help

[msvoboda27]

I have an excel file that I am trying to do a calculation only if value of 'Closed" isn't present in another cell. Closed is a status I'm using in a drop down.

Trying to calculate if "Met SLA" or 'Did Not Meet" only if status closed is not included,

I think I need to consolidate a Search and If statement,
Here's the two formulas that work in 2 different cells

=IF(ISNUMBER(SEARCH("Closed",M6,1)),"Exclude","Completed")
and this statement

=IF(W6<=72,"Met SLA"," Did Not Met")
Thank you for your time.

 

[jtakw]

Hi,

Not exactly sure what you want, below is formula that if "Closed" is Not in M6, then test W6 for results, but if "Closed" IS in M6, then what?
Please describe in detail what you need the formula to do.

Book1
	C
1	Met SLA
Sheet60

Cell Formulas
Range	Formula
C1	=IF(NOT(ISNUMBER(SEARCH("Closed",M6))),IF(W6<=72,"Met SLA","Did Not Met"),"Whatever you want here")

 

[msvoboda27]

Hi and thank you for your support.
I have a project that I am calculating hours to complete a help desk ticket only if the status is closed.
I think I described my request backwards. IF the status is closed then I can only calculate either "met SLA" or "did not meet" otherwise the cell should display "Exclude"
Here's the screenshot and the statuses im using in my drop down
https://ibb.co/cb8wwH

PS. Here's the formula I updated from your guidance:

=IF(NOT(ISNUMBER(SEARCH("Closed",M6))),IF(W6<=72,"Met SLA","Did Not Met"),"Exclude")

I hope that makes more sense.
Thanks again!

 

[jtakw]

Still not clear on what you want, based on your latest description, you can use the following formula.
When would it display "Completed"? or is that out and no longer needed.
Your screenshot didn't help neither, as you have the drop down blocking the two cells that would help explain what you're looking for.

Book1
	D
1	Exclude
Sheet60

Cell Formulas
Range	Formula
D1	=IF(ISNUMBER(SEARCH("Closed",M6)),IF(W6<=72,"Met SLA","Did Not Met"),"Exclude")

 

[msvoboda27]

Hi again,
Thanks for your time and really making this process easier and also being so patient!
I have made some new edits and even have made the document even more robust/smart.

Below is the updated formula.
The last open item trying to solve is if the incident resolve date cell is empty then should reflect Exclude but now it shows Met Sla, row highlighted in blue. I thought the "OR" function would work. 72 hrs or less OR 0 hrs in column W.
I would be happy to email the file for one row if that is an option.
Here's the screen sprint, hope it shows all info needed.

=IF(ISNUMBER(SEARCH("Closed",M6)),IF(OR(W6<=72,W6=0),"Met SLA","Did Not Met"),"Exclude")

https://ibb.co/eHm6WS

 

[jtakw]

See if this achieves what you need:

Book1
	E
1	Exclude
Sheet60

Cell Formulas
Range	Formula
E1	=IF(OR(W6="",NOT(ISNUMBER(SEARCH("Closed",M6)))),"Exclude",IF(W6<=72,"Met SLA","Did Not Met"))

Hope I am understanding correctly.

 

[msvoboda27]

Thanks for response. I did try your updated formula however, no change.
https://ibb.co/jyvBFn
In the date resolved field (column u) I still show the Met SLA value instead of exclude when i have no value in that field.
Again thank you for your support

 

[Aladin Akyurek]

Try to post the sample (from your image) as data directly here.

 

[msvoboda27]

Thanks for response, happy to do that, Can you direct me how I can do that? I am new to the forum. I checked the FAQ and it was more about soliciting business etc.

 

[Aladin Akyurek]

Thanks for response, happy to do that, Can you direct me how I can do that? I am new to the forum. I checked the FAQ and it was more about soliciting business etc.

See:https://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729