formulas and functions
25337 TopicsCounting non identical columns
Could anyone explain how to count how many, or remove non-identical entries there are in a set of columns? e.g. In this table, in lines 1- 4 column A and B are identical, but lines 5 - 9, column A is the same in each but column B has a different value in line 9 than in lines 6-8. I want to find a way to count lines 1-5 as one entry and lines 5 - 9 as two entries (i.e. same code in all five lines but different date in one of them.) so the number of unique entries is 3. Line A B 1 G123456 01/01/2026 2 G123456 01/01/2026 3 G123456 01/01/2026 4 G123456 01/01/2026 5 H123456 03/01/2026 6 H123456 03/01/2026 7 H123456 03/01/2026 8 H123456 03/01/2026 9 H123456 05/01/202615Views0likes1CommentFormula for adding multiple sheets
I am trying to add multiple things across multiple sheets. example : On sheet one, I have column a date, column b check number, column c name of place, column d category, column e withdrawal amount, column f deposit amount, column g balance, column h status (cleared or pending). this same information in on sheets 2,3,4,5 and so on. I want to create a sheet that will tell me how much I have spend with each name of place (column c). let’s say sheet one looks like this 3/23 (date) 777 (check number) John Doe (name of place) 2.00 (withdrawal amount) etc. 3/23 778 John Doe Jr. 3.00 etc sheet two 3/24 666 Joe Doe Jr. 3.00 3/24 668 Joe Doe 4.00 on my total sheet with the amount I have spend with each person/place to look like this Joe Doe — 6.00 (from the 2.00+4.00) Joe Doe Jr — 6.00 (from 3.00+3.00)51Views0likes2CommentsMO 365 Excel - Difficulty Changing a Date format
I am using MO 365 - apps for Business, currently received an Excel Spreadsheet and am attempting to change date format in Excel and have tried all the usual methods however the dates remain unchanged. I need to change the date from month /day/year as 1/31/2025 to Year/Month/Day 2025/1/31 Any Suggestions? Thanks CJ58Views0likes2CommentsCreating weekly calendar template that has overlapping positions
Hi, I have a software for individuals to sign up for shifts, but it is visually hard to see where there are open areas that need to be filled. I need help creating a calendar from this software download that I can update each week. It would have to be much like an hourly scheduler view to easily see when someone is coming on and off shift. But also laid out from Sun-Saturday. There is 3-4 "slots" for each day. It would be great if they could be color coded as well. Slot 1 needs to be anyone that is "AEMT" in the position column, Slot 2 can be anyone listed as "EMT" or "EMR" (caveat to this would be that slot 2 could also be AEMT if there is already an AEMT in slot 1 for the same time slot. Slot 3 is for driver or ride along (or the other 3 positions if there is one listed in the other slots), and possibly 4th slot would just be overflow in case there were 4 people signed up which does not happen very often. Is there anyone who can help me with designing this? Below is what the software generates when converting to excel131Views0likes5Comments#DIVISION/0!
Hi. I have a problem (this is in Swedish) where I get #DIVISION/0! in a field in excel. The formula in the field is =AVRUNDA(MEDEL(G4:G22);2) Translated I think it is =ROUND(AVERAGE(G4:G22);2) I have tried to do as you described with =IFERROR(AVRUNDA(MEDEL(G4:G22);2),"") But I get an error message.38Views0likes2CommentsSelect from multiple conditional format rules, based on value of another cell.
I have a spreadsheet tracking due dates for deliverables, depending on what department a deliverable is for, the acceptable time taken from order to delivery varies. On Column H, I have a formula that calculates how many days OVER the acceptable time a delivery is, I want a conditional format that colours that cell on a gradient from 1 to 150, going yellow to deep red. So a quick visual inspect will show which ones are going to be a major problem. The difficulty being the gradienjt scale will have to change depending on the department that deliverable is for. So in Column C I have the department name, this gives Column D data to look up in a config tab, that lets it check how many days is considered late using a basic Vlookup ona small table that is just two columns Dept Name, and acceptable days late number. =IF(C2="","",VLOOKUP(C2,Configuration!D$4:E$6,2,FALSE)) Then Column H looks at the order date(Col F) and delivery due date (Col G), and checks if the time between is over or under the acceptable range with a simple comparison and show how many days (If any) late the delivery will be. =IF(G2-F2>D2,(G2-F2)-D2,"") I want Column H to be conditionally formatted with the mentioned gradient, however the conditions for that gradient will change depending on whats in the Department Column C, some departments consider 14 days over to be acceptable (coloured yellow), some departments see 8 days as a critical issue (Coloured deep red). So my question is how I apply a different Conditional Formatting Rule to the cell in Col H, depending on the value of Col C. If thats even possible. I know I can do it with a macro, but I want to try keep this worksheet formula's only if possible as the person using it does not use VBA and wouldn't know how to troubleshoot if something went wrong, whereas I could explain a formula to them.69Views0likes2CommentsHelp Clean Up This Formula
Hello, I am looking for a way to compact this formula more. I have about 20 rows I need to do. =C25-IF(OR(COUNTIF(D3,{"A","B","C"})),C3,0)-IF(OR(COUNTIF(D4,{"A","B","C"})),C4,0)-IF(OR(COUNTIF(D5,{"A","B","C"})),C5,0) I am trying to minus the amounts in column C# if Column D# = conditions A, B or C from C25. The math is all happening in one cell. Both columns are 3 through 24. I am hoping there is a way or am I SOL and have to do each row by hand? Thank you, CaseySolved83Views0likes4Comments