Get notified when four cells met my requirements.

[razhmo7al]

greetings every one,

i hope you are well and safe. This web helped me a lot so, thank all of you for your support. i have a small problem that might be solved here of course. i have 4 types of equipment listed in the excel sheet using the PI add-in tool to extract the running hours for this equipment. Now this equipments should not be run in the same time, only 2 of them can be run in the same time and the other will be standby. but they should not be stopped more than 480 hours in the last month (-30days). so how i can get notified if these equipment needs to be switched and make sure they are not stopped more than 480 hours. i tried to use (=if(or(and) but it will not help at all. i hope you got my idea. please ask me if something went wrong.

Thanks

 

[Col Delane]

Try this.

Separately for each piece of equipment, use the SUMIFS function to calculate the number of run hours:

• sum the entire range of run hours (Sum Range)
• where the corresponding date (Criteria range1) is greater than Today-30 days [ ">=" & EDATE( Today(),-30) ] (1st criterion)
• and where the corresponding date (repeat Criteria range1 for Criteria range2) is less than Today [ <Today() ] (2nd criterion) .

Then wrap the above formula inside =IF( SUMIFS( as above ) < 480, "< 480 hrs -->Run!", ">=480 hrs -->Ok" ) or something similar.