formulas and functions
25322 TopicsFiltering an Array based on a list of Criteria
I have an array that tracks every project that every project manager, project engineer, and superintendent is on and I am trying to set up a function to search through a single list of names and return just their projects. The PM, PE, and SUP on the array all have their own columns since there can be 1 of each on a single project. and the list im pulling from is within a single separate column pulling on the names I select. I have had success pulling from one column at a time using a FILTER(ISNUMBER(MATCH)) function but I cant get it to search and return all values from all three columns. Any thought on how to expand the search? Thank you!15Views0likes1CommentUsing calculated columns in pivot table
I already used calculated fields to get some datas in my pivot table. They're displayed as count or sum. I want to make a new calculated fields using a Sum of working time, divide it with the number of operations. How can I use the count and the sum ? Right now, it doesn't seem to use the sum nor the count : Here I want Sum of MBTF to be : Sum of working time/ Count of Stop but the values in Sum of MTBF seems to use the values in Stops (they are dispalyed in my original table as 6/7 numbers, but I used the function count to only get the number of occurences). I believe that's the thing causing my Sum of MTBF to show 1.68E-07 numbers ( for the first line it should be 52.001) : How can I get to have a column displaying the right values (52.001, and others for each line) ? Thank you in advance for your help !47Views0likes1CommentFinding return value from multiple columns/cells with specific text
Hello, I have to verify multiple cells (C2 to F2) with two possible statuses: "Eligible" and "Not Eligible". In Excel, would it be possible to: If all cells "Eligible", then the result is "Passed". If any of the columns is "Not Eligible", then the result is "Failed". (additional issue) this is not super important but it would be helpful to add one more condition: if any of the columns is "Consult", then the result is "No Result". I'm fine if only the 1st and 2nd issue works, though. I have tried finding solutions in other discussions, but usually the formula that I copy-pasted keep showing "Passed" even if one of the documents is listed as "Not Eligible". Thanks in advance. A B C D E F G 1 No. Name Document 1 Document 2 Document 3 Document 4 Result 2 1 Candidate A Eligible Eligible Eligible Eligible Passed 3 2 Candidate B Eligible Not Eligible Eligible Eligible Failed 4 3 Candidate C Eligible Eligible Consult Eligible No Result 5 4 Candidate D Eligible Eligible Eligible Not Eligible FailedSolved52Views0likes4CommentsFile always corrupt
Anyone have the same problem with me? I use microsoft excel 365 on my mac. At the beginning everything alright, until i use formula and then save the file. When i want to re-open the file, the messagebox showed up "we found something wrong in your content...". It gives 2 option, yes and no. If i click no, the file wont open. If i click yes, the file open, the excel do the recovery, the file open but end up with all the formulas is gone, deleted by excel and just left the value. What should i do? Do the file can still be fixed?214Views0likes5CommentsExcel repair strips all formulas from large .xlsm after March 2026 security update (KB5002849)
Hi everyone, I'm a master's student at Karolinska Institutet in Stockholm. My thesis is a health economic cost-effectiveness model built entirely in Excel — a gender-neutral static Markov cohort model with 34 worksheets. The file has become completely unusable after what I believe is the March 2026 security update, and I'm running out of options. The file: - .xlsm, ~46.5 MB compressed, ~370 MB uncompressed XML - 34 worksheets, four of which are 73–92 MB each (Markov trace sheets) - ~65,000 formulas, ~33,500 shared formulas - Heavy use of LET, LAMBDA, XLOOKUP, XMATCH, CHOOSECOLS, TAKE, MAP, SWITCH - 771 defined names including ~147 hidden _xlpm.* LET/LAMBDA variable placeholders - Stored on OneDrive via KI SharePoint, 34,000+ AutoSave revisions - Contains VBA (vbaProject.bin) The problem: Every time I open the file — on Excel for Mac or Excel Online — the repair engine triggers and strips ALL formulas from every sheet, replacing them with cached values. The file shrinks from ~46.5 MB to ~26 MB. Clicking "No" on the repair dialog just closes the file. There is no way to bypass the repair. What I've verified: - Extracted the .xlsm as a ZIP and confirmed all formulas (<f> tags) are fully intact in the raw XML - Libr€Office Calc can read the formulas but cannot execute them (Err:508 — no LET/LAMBDA support) - Removed 158 broken named ranges (#REF! and #NAME? entries) from workbook.xml and rebuilt the archive — repair engine still strips all formulas - The issue reproduces on every OneDrive version history copy (up until I largely used LET formulas in my sheets - but there is still 1,5months of changes lost) - The issue reproduces on both Excel for Mac and Excel Online Suspected cause: The March 10, 2026 security update (KB5002849) patched CVE-2026-26108, a heap overflow in Excel's file parsing during loading. The same patch was applied to Office Online Server (KB5002846). I believe the tightened parsing now rejects or flags my file's large XML structures as potentially malicious, triggering the repair engine to strip all formulas. This is consistent with: - The known _xlfn. namespace bug on Excel for Mac (reported by multiple users on Microsoft Q&A since late 2024) - The timing - the file was working before this update flawlessly up until March 16th - The fact that Excel Online is also affected (same server-side patch) My questions to the community: 1. Has anyone else experienced formula stripping on large workbooks after the March 2026 update? 2. Is there a way to bypass the repair engine on Mac, or roll back the specific security patch without downgrading all of Office? 3. Would opening this file on Windows Excel (pre-patch or current) preserve the formulas? If anyone with a Windows PC would be willing to try opening and re-saving this file, I would be incredibly grateful. 4. Is there now effectively a size/complexity ceiling for Excel workbooks that makes models like this unviable? If so - should I be migrating this to another environment (R, Python, etc.) going forward? This file represents six months of thesis work. The formulas are all there in the XML. I just need Excel to stop destroying them on open. Any help, pointers, or similar experiences would be hugely appreciated. Thank you, Florian Boschek5Views0likes0CommentsNeed a consecutive XLookup
Hello All, OK let me describe what I am trying to do: I need to create a Task List. In this list I will have let’s say 200 tasks, and 50 people to perform these tasks. i need a way to assign the first task to the first person, then the second task to the second person, then the 3rd task to the 3rd person and so on until the first 50 tasks are assigned. then I need to assign the 51st task to the first person, the 52nd to the second person, and the 53rd task to the 3rd person until that batch of tasks and People are matched. but this is not linear, I may want to choose task 52 to person 3 because the 3rd task is related to the 52nd task. i can live without this feature and fine tune the list manually but the main question is how can I look up a name and match it to a task. i hope this makes sense to you, please ask for any clarification and i shall add that bit. thanks in advance… wassim85Views0likes3CommentsLook up a date range and return title from list of date ranges it falls within?
I searched and didn't quite find this answer, hoping somebody can help. I would say I'm a novice with Excel, but can generally follow directions well enough. I have a list of from and to dates. I also have a list of established date ranges, each titled. I would like to look up each of my date ranges to find if they fall witin any of the date ranges from the list and then pull in the appropriate date range title. Example: List of established date ranges: From Date To Date Range Title 1/1/2026 1/15/2026 Jan 1st Half 1/16/2026 1/31/2026 Jan 2nd Half 2/1/2026 2/14/2026 Feb 1st Half 2/15/2026 2/28/2026 Feb 2nd Half Data I'd like to look up within those ranges: From Date To Date 1/5/2026 1/8/2026 2/5/2026 2/11/2026 Expected Results: From Date To Date Range 1/5/2026 1/8/2026 Jan 1st Half 2/5/2026 2/11/2026 Feb 1st Half Any advice welcomed.54Views0likes2CommentsHelp with summing totals using multiple texts
Good evening. I have attached an image of my spreadsheet below which hopefully will help my blurb make a bit more sense! I am producing some uniform for a local club. I have made a spreadsheet to allow me to track the number of garments and sizes ordered (purple table). However from this information in the purple table I need to know how many logos of each size to order (orange table). I know what size logo is required for all the different garments and sizes of garment, as can be seen in the blue table, but this would require me to go through 1 order at a time tallying up what size logos I require. Ideally I would like excel to be able to look at the purple table and tell me how many items there are that require a given size logo. For example, I know that a 'Youth S T-shirt' and a 'S Legging' and 'Youth M Hoodie' all require a 6cm logo. and also that the 'Youth M Hoodie' also requires an 18cm logo. I would therefore like excel to look at the purple table and see that there are 3 x 'Youth S T-shirt', 2 x S Legging and 2 x Youth M Hoodie, therefore 7 items require a 6cm logo, so I would like excel to populate the orange table in the 'total needed' column for 6cm (cell B23) to show '7'. The 'Youth M Hoodie' also requires an 18cm logo and therefore cell B28 should also populate with '1'. I have tried COUNTIF, COUNTIFS, SUMIF, SUMIFS, but none of them have worked. I do not know if I am entered the information into the equation incorrectly or if I am using the wrong function/formula. Can anyone please help! Many thanks.80Views0likes4Comments