Forum Discussion
Formula Help
You did not tell us which version of Excel you are using, or on which platform (Windows, Mac…).
The main problem is that your SUMIFS formulas are comparing the start hour and end hour to the entire range of scheduling data (rows 25 to 45) of values, rather than to the rows in the appropriate section (end hour should be compared to rows 25 to 35 for Start data, and start hour should be compared to rows 36 to 45 for Finish data).
See the attached workbook for multiple solutions.
SnowMan55 Thank you very much for your rapid response and support. Your sumifs solution has worked instantly.
I'm building this on my Mac, but it will mostly be used on Windows at work I am not sure of what version it has, but it seems relatively new, most of the work is done on the web through a share point so I would assume this side of it is 365, so needs to work across all platforms, which I hope it does after all this.
Do you have any guides on mass conditional formatting, is there any quick way of doing this? The table you have helped resolve needs to reflect these values on another sheet that will be used daily, when there is a one value, I need the cells to be clear, but if the value is zero, I need the cells to be blacked out.
As you can see below, I have started doing each block, but this needs replicating 12 times per hour for 13 hours to read each cell in the first table, so screens 1 to 3 are blacked out, and 4 is open and clear.
I've tried searching online, but what I have seen doesn't work with grouped cells like I have, and unfortunately, I cannot change the layout of the sheet.
Any guidance would be greatly appreciated.
Thank you
Martin
The merged cells do add some complexity to the conditional formatting. See the _Info worksheet of the attached workbook for a description of my technique.
In the future, either attach a sample workbook (with sensitive/proprietary data replaced or removed, of course) to the post. Or load the file to Microsoft OneDrive, Google Drive, DropBox, or another file sharing service, set the security to make it readable by everyone, and include a link to that copy in your post. You should not expect other forum members to convert your image(s) to a workbook.
