Need help with a possible easy formula and to either MATCH or VLOOKUP or something similar

Hello All!!,

I have what I think may be an easy formula request. I just am not able to figure it out. (please see the attached sheet to follow along)

The cell layout must stay as shown for sheet 1, so unfortunately there may be an easier approach if I move cells and columns around but it can't be done.

Here is the scenario. Sheet one provides a quick summary of status for a specific room that is updated on Sheet 2. It shows the total number of machines that are OFFLINE and are in the yellow cells. The blue cells have other info that must be fixated there, but I just changed the color to show that only the yellow cell is required.

So if I were to just do a formula of one cell = another and drag it across as we update per day, Sheet one doesnt reflect that as i copy a new block of info into the next day as we begin. Is there a formula where Sheet one MATCHES the date on SHEET 2 to give the number of machines OFFLINE?

I am trying to make it able so that I can copy paste the 4 columns in SHEET 1 into the next 4 columns to keep the formula working. IF this is possible this would be so much appreciated!!

Thank you all!!

Pete!!!

This works perfectly. If possible, could I complicate it further. (The formula you provided will work perfectly) but I wanted to see if in the future depending on the data, I could expand on the sheet.

I have attached an edited spreadsheet that includes more info/changes for future progress. So, initial issue was with one room, but what if we have two rooms with different sets of MACHINES? In the new spreadsheet, on SHEET 1, the yellow cell is for ROOM1 and green cell is for ROOM 2. On SHEET 2, how could I capture when MACHINES are anything but ONLINE. I included HOLD on SHEET 2. SO, for example:

On 1/21, SHEET 1 would indicate ROOM1 with zero and ROOM2 would indicate 1. Since one MACHINE is on HOLD.

Same thing though with the dates. Just as you expertly included, I could copy and paste into the next four columns and the information would be captured.

You've done brilliantly with the first request. If possible, could you help with this one as well?

THANK YOU so much for the other formula!!!! This is already further than I could of gotten.

Thank you PETE!!!