Forum Widgets
Latest Discussions
Two way sum lookup (by month & name)
Hi all I am trying to perform a two way lookup from a source table. Basically I have names of colleagues with a number of days per week recorded. I want to then in another table using a formula sum the total amount of days by colleague for each month. I tried using SUMIFS, but it throws a VALUE error as I think the SUM range is not accepted across multiple columns/rows. Any ideas? I attach a sample spreadsheet. Thank you in advancematt0020190Feb 11, 2025Brass Contributor42Views0likes5CommentsTimesheet Tracker Help with INDEX/COUNT/SUM functions or alternative
Hi! I have inherited a series of timesheet trackers that I'm hoping to identify a formula or solution to easily quantify the PTO classifications that an individual employee took throughout a year. I have uploaded a scrubbed example of the workbook in question with a small sample of the biweekly timesheets that the previous coordinator used, as well as some screenshots at the very bottom. The MASTER worksheet is one that I created before I ran into a whole slew of errors ranging from #SPILL to #REF to #ERROR before deciding to take it to the forums. Ideally, I need a formula that will: Match the employee name in D2:D40 of the "MASTER" worksheet with those in B6:B44 in each of the sheets that follow (the scrubbed example has 4, but the full workbook has 26, which aligns with the "Sheet Index" in A2:A27 of the "MASTER" worksheet. Count occurrences of each PTO classification (from E1:M1 in the MASTER worksheet) referenced in those sheets so that I can capture how many times each matched employee took "HOL"; "PER"; etc. There are some discrepancies in the references that my predecessor noted in the cells, such as "-4" to note a half-day. Any help/guidance on this would be vastly appreciated as I have 26 worksheets to quantify for the last 3 years for compliance and really trying to avoid doing it manually. I'm also open to changing the format/layout of the "MASTER" worksheet to best approach a solution as well. Let me know if you have any questions! Many thanks in advance. Example screenshots:tshprdFeb 11, 2025Occasional Reader57Views0likes3CommentsRepeat list of items for each month in table
Hi all I am looking to have an automatic repeating list of items that repeats based on the amount of months in a table. If however a month is mentioned in the list of items, the item will only repeat for that month only and not all the months in the table. Please see my example spreadsheet with the desired output, which should make it clear. I just need a formula to make this output table. I guess LET would be possible, but can anyone help please? Thank you Mattmatt0020190Feb 11, 2025Brass Contributor102Views0likes10CommentsCannot edit conditional formatting formula.
I am unable to edit a conditional formula. If I have a formula =OR(B1>C1,C1>D1,D1>E1) and I want to change the formula to be =OR(B1>=C1,C1>=D1,D1>=E1) I cannot put the cursor after the ">" nor I can use the left/right arrow keys to move the cursor there. Using the left/right arrow keys results in entry to cell references into the formula. I have to erase the entire formula and start over. Drives me insane. How do I put the cursor in the middle of the formula or move it to where I need it? (The huge cell reference numbers are created by Excel, another pain the ...). Yes, I can see that the cursor movement keys enter the reference to a cell, but I want to EDIT the formula, not have Exel so helpful I am nauseated. Any help with this would be much appreciated.NummerSechsFeb 11, 2025Brass Contributor20KViews6likes16CommentsExcel filters with live and non-live data
Hi, I have created a spreadsheet for our attendance tracking. The spreadsheet contains some live student and attendance data pulled from our MIS, alongside non-live columns containing weekly comments and attendance categories (excellent, at risk, etc.). This non-live data is not held within our MIS so cannot be part of the liva data feed. I need to be able to either filter or sort the data by attendance category. When I try to do this when highlighting the entire dataset, the functions are not available. This, I presume, is because the live and non-live data columns are acting as two separate entities. I can filter the attendance category but this doesn't also sort the live student data (so the attendance category is not related to the student data), or vice versa. I can extract the data as values into a different worksheet then filter, but this is time consuming and needs to be re-extracted each time the data feed is updated. Is there a way to filter from the original live and non-live data together (i.e. I could filter by absence category and this will also filter the live student data in the related rows)? (We do have PowerBi but we need to be able to have this in Excel to add weekly comments relating to attendance). Thank you in advance!CToddFeb 11, 2025Occasional Reader11Views0likes0CommentsSorting dates in Excel from oldest to newest is not working
Hi all, I would like to sort a date series in Excel, formatted as Mon Feb. 10, 2025 08:59 AM, from oldest to newest, but I can’t get it to work. Every week, I have to download a list from a program that uses this date format (see screenshot). I want to sort it in order from oldest to newest, but when I try to do so, the dates get mixed up. Downloaded list: 2. I select the cells and want to order it from old to new, then it gives me this: Does anybody has a solution for this? Thanks so much in advance! ArianneArianneCFeb 11, 2025Occasional Reader48Views0likes4CommentsCOUNTIFS to return values in a table
Hello! Working with a large dataset and seeking to break up the data into different columns based on amount. I am looking to separate projects <100K, projects >=100K, projects <=1M, and projects >1M. When I use the COUNTA function, I get a #Spill! error. When I use COUNTIF or COUNTIFS (to specify the 100K<=x<=1M), my output for managers that don't have projects that meet the criteria is '1', rather than the specified 'None'. Here are a few of the solutions that I have tried: =LET( TPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")), CurrentPM, A2, FilteredBudgets, FILTER(Table_Projects[Current Budget GR], (Table_Projects[Project Manager]=CurrentPM) * (Table_Projects[Period]=MAX(Table_Projects[Period])) * (Table_Projects[Current Budget GR]>100000) * (Table_Projects[Current Budget GR]<1000000) ), ProjectsCount, IFERROR(COUNTA(FilteredBudgets), 0), IF(ProjectsCount = 0, "None", ProjectsCount) ) This one gives me a formula error: =LET( TrPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")), CurrentPM, A2, FilteredBudgets, FILTER(Table_Projects[Current Budget GR], (Table_Projects[Project Manager]=CurrentPM) * (Table_Projects[Period]=MAX(Table_Projects[Period])) * ), ProjectsCount, COUNTIFS((FilteredBudgets, ">100000") * (FilteredBudgets, "<1000000")), 0), IF(ProjectsCount = 0, "None", ProjectsCount) ) This one returns the correct project number between 100K and 1M, but does not return "None" for projects that don't meet the criteria. =LET( TrPMList, FILTER($A:$A, ($A:$A<>"") * ($A:$A<>"Project Manager") * ($E:$E<>"N/A")), CurrentPM, A3, FilteredBudgets, FILTER(Table_Projects[Current Budget GR], (Table_Projects[Project Manager]=CurrentPM) * (Table_Projects[Period]=MAX(Table_Projects[Period])) * (Table_Projects[Current Budget GR]>100000) * (Table_Projects[Current Budget GR]<1000000) ), ProjectsCount, COUNTA(FilteredBudgets), IF(ProjectsCount > 0, ProjectsCount, "None") ) Any help is greatly appreciated!!SolvedburnskrlFeb 11, 2025Copper Contributor58Views0likes7CommentsAutomatic Rolling 12 Month List
Hi all I've looked first but nothing seems to give me what I am looking for. Hope you can help! I'm simply looking for a rolling 12 months list that automatically updates based on the current date. The financial year starts in March. An example below based on today's date being in February 2025: Column A January 26 February 25 March 25 April 25 May 25 June 25 July 25 August 25 September 25 October 25 November 25 December 25 The idea being that once the month has passed, February 25 would become February 26 in the list and so on. Can this be done with formula based on the today() date? Thank you for any helpSolvedmatt0020190Feb 11, 2025Brass Contributor93Views0likes7Comments- ajmal_pottekattil_yoousufFeb 11, 2025Iron Contributor94Views1like7Comments
SUMIF Problem
I’m trying to use excel to keep an accurate count of inventory for our small church bookstore (non-profit). I decided to use UPC codes on my spreadsheet so that when I scan an item that is being sold the Product name, selling price would automatically populate my spread sheet from a VLOOKUP table. It took some work but that seems to be working. Now I’m trying to have the worksheet automatically up-date our current stock amounts when an item is sold or we buy new/additional items for the bookstore. I’ve set up a separate tab called IN-OUT Inventory where I record the quantity of items sold or added to the inventory. When I scan a UPC code and it pulls over the Product name and selling price and I enter either a negative number for a sale or a positive number for addition to inventory. My main tab is called Current Inventory Sheet and I’ve placed a column at the end of that spreadsheet called Beg. Stock to represent the stock we are starting out with for this year. In the Current Bookstore Inventory tab the “Total Stock” column I need it to add in the beginning stock and then search for the UPC code on the In-Out Inventory sheet. If there is a match then add or subtract that number to reflect the current stock. I thought I had this working with a SUMIF coding but it appears that it won’t add or subtract additional transactions when there is a UPC code recorded more than one time on the IN-OUT sheet. So if I start out with 5 widgets and sell 2 it records total stock as 3; however, if I then receive 20 widgets and/or even sell 2 more it doesn’t recognize that transaction. Any help anyone could provide is greatly appreciated. Thank you! Here’s the link to the document: Church Bookstore Inventory Doc.xlsxMariePFeb 11, 2025Occasional Reader31Views0likes3Comments
Resources
Tags
- excel42,181 Topics
- Formulas and Functions24,452 Topics
- Macros and VBA6,348 Topics
- office 3655,932 Topics
- Excel on Mac2,616 Topics
- BI & Data Analysis2,330 Topics
- Excel for web1,878 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,607 Topics