Help needed with IF and COUNTIFS Formulas
Is anyone able to advise the following formula: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & V3,$D5:$D15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & V3,$H5:$H15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & V3,$L5:$L15, ">" & V3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & V3,$P5:$P15, ">" & V3) Is there a way to simplify this? Is there a way to make this more accurate? Cells in column G & H, I & J, O & P are using the following format: =IF(C6="","",C6+E6) Cells in U4:CC4 are using the following format: =COUNTIFS($B$5:$B$15,$R$4,$C5:$C15,"<=" & U3,$D5:$D15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$G5:$G15,"<=" & U3,$H5:$H15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$K5:$K15,"<=" & U3,$L5:$L15, ">" & U3)-COUNTIFS($B$5:$B$15,"="&$R$4,$O5:$O15,"<=" & U3,$P5:$P15, ">" & U3) Cells in U5:CC15 are using the following format: =IF(U$4>=$T5,1,"") My issue is is when I put in the three break times, the mid break comes out at a shorter time. My other issue is is that when I put in the times in row 5,6and 11, the data is coming up as a combined data in rows 5, 6 and seven on the page two. Just for reference, "page two" is the same spreadsheet. What I need to happen is that I enter in the shift start time and finish time. This then populates through to Break 1, 2 and 3. The Time entry is the time the break starts. ie: 1 hour after start of shift, 1 hour after coming back from break, etc. The break entry is the duration of the break taken. ie: 30 minutes. Once all the info is put in, the relevant "Time Block" on "Page 2" shows a 1. What is happening at the moment is that when I enter all the time data, the time blocks are not populating correctly in accordance to the entry. Basically, If I have numerous people on shiftI need the time blocks to show where I have shortfalls in shift cover and not having too many people on break at the same time. IE: Link to Live Copy: https://www.dropbox.com/scl/fi/eur1j526htu1j8a4d4290/Staff-Breaks.xlsx?rlkey=r4tm9xts4tonofpa2th2cusfw&st=nueyk0d7&dl=0 Any ideas would be greatly appreciated.Excel formula to take time off from a time range
I am trying to get Cell D2 to give me a result based on the result in C2. I have 4 different time "zones" Each time slot has an amount of time taken off so if the employee starts at 7am and finishes at 11am they would be in the system for 4 hours. On 4 hours they get a 15 minute break and so will only be paid for 3 hours and 45 minutes. the formula works fine with one instance but as soon as I string the rest it goes hay wire. Any help / advise would be greatly appreciated. The formula I am trying to run is as follows: =IF(AND(C2>=TIME(4,0,0),C2<=TIME(5,59,0))*C2-TIME(0,15,0),AND(C2>=TIME(6,0,0),C2<=TIME(7,59,0))*C2-TIME(0,30,0)*AND(C2>=TIME(8,0,0),C2<=TIME(8,59,0))*C2-TIME(0,60,0)*AND(C2>=TIME(9,0,0),C2<=TIME(11,59,0))*C2-TIME(0,90,0))
Cell drop list of workbook sheets
Hello Is there a way to have a drop list of all the sheets in a workbook in selected cells and the selected sheet from the list, populates other cells in the first sheet? I want to easily see what machines are connected to a list of RJ45 wall points. In the workbook, there is 1 sheet that has a list of wall point and each machine has its own sheet. Each machine sheet has lots of corresponding data that is not pertinent to the RJ45 wall point sheet. My goal is to select a cell next to a given wall point row in the RJ45 wall point sheet, that drops down a list of machines (that are all the machine sheets in the workbook). The dropdown list selection will populate pre-defined adjacent cells with corresponding data from the selected machine sheet. Each cell in the MACHINE NAME column has a drop down list of the sheet names to select from. Once a machine is selected, the hardware names and model columns that are on the same row are populated with the corresponding data on the machine sheet.
Not able to count with COUNTIFS
I am having problem counting with COUNTIFS In a column of an Excel table =TEXT([@[Date: Referral Rx]], "mmmm") This shows the month in the column Then, I am counting the total of those in the current month with the following code: =LET( targetDate, TEXT(TODAY(), "mmmm"), COUNTIFS( Table[Month: Initial Referral Rx], targetDate ) It is not counting it. I get 0. I tried COUNTIFS(Table[Month: Initial Referral Rx], TEXT(TODAY(), "mmmm")) It also didn't work. Need help. Thank you in advanced.
Hyperlinks not working on Macbook air
I have had an issue with permissions for accessing files with excel, in my Macbook Air (Apple M4 - Tahoe 26.2) for almost a year now. When I insert a link to a pdf and then I try and open the link, excel does one of three things. It either warns me that some files may have viruses etc etc and when I click open it opens the file it tells me that opening the file may cause issues etc etc and when I choose to open the file, it goes into the message above (1) and eventually opens the file it asks me to grant access to the file and it shows the file path and I need to click select, then it goes to the folder where the pdf is located and I need to click grant access - but when I do, it says that the file cannot be opened. I have gone into my system settings and excel has access to my full disk and files and folders. I have googled the issue and it was suggested to give excel access to the particular folder where the files are located but I don't know how to do this, other than in system settings. I have tried clicking on the folder where the files are located when option 3 above has come up but when I click on the folder, the grant access button at the bottom is greyed out. Can someone please suggest something, as it is driving me crazy now. I have inserted the links through the 'insert', 'add a link' button on the excel menu. Thanks.
Too thick/fat gridlines when printing from excel
When I print a spreadsheet where I have selected/marked for gridlines, the gridlines become much thicker than they should be. They have the same thickness as the cells I have given borders. On the screen everything looks correct. The problem is only on the printout. I get the same problem no matter which printer I choose (has nothing to do with the printer driver). I have the latest operating system and drivers.
pivot table
In recent versions of Excel 365 for Mac, the drag-and-drop behavior in PivotTables has regressed significantly compared to previous releases. Specifically, when attempting to move a field from the Row or Column area into the Filter area, Excel often interprets the action as a removal rather than a relocation. This breaks the intuitive manipulation of Pivot layouts that has been standard for years. Additional regressions include: Reduced responsiveness of the Field List pane Inconsistent behavior between older PivotTables (created in previous versions) and new ones Hidden or unavailable “Classic PivotTable Layout” options Lack of visual feedback when dragging fields between areas Increased reliance on context menus for basic layout changes Incoherence between Mac and Windows versions of Excel These changes hinder productivity for advanced users who rely on fast, flexible layout adjustments. Suggested improvements: Restore or make optional the classic drag-and-drop behavior Ensure consistent handling of field movements across all areas (Filter, Row, Column, Data) Improve visual cues and drop zones during field manipulation Guarantee parity between Mac and Windows versions Excel’s PivotTable interface used to be a model of intuitive design. Please consider restoring that flexibility — especially for users who build and modify complex reports daily.
Moving a column of text data into 3 columns of data?
I have a column of text data cells 1,2,3,4,5,6,7,8,9 and longer. I want to create 3 column of data to graph and manipulate Cell in Columns. 1,2,3 3,4,5 5,6,7 8,9,10 and longer. So i need to create 3 columns of data from 1 column of data. I am using Mac Excel 16 and I can not make this happen. I have tried all sorts of solutions. Help? Thank you,
