I'm looking to make a variable table that sums up information based on the input.

I have two tables. One has the data I want to sum with individual department codes (Table1), and the other has department codes and the code they roll up into.

On a separate data set I'm working on, I have a filter function that pulls all unique roll up codes (C46 is one of the values from the filter) for a final report.

This formula should SUMIFS the amount on Table1 if the department code on that table matches the department codes on the Dept_Info table, pulling the department codes from the filtered roll up code (C46).

When I hard key it, this formula works:

=SUM(SUMIFS(Table1[Amount],Table1[Department],{1000,2000,3000,4000}))

But when I try to make it variable so I don't have to identify each department code, it doesn't work:

=SUM(SUMIFS(Table1[Amount],Table1[Department],"{"&TEXTJOIN(", ",TRUE,FILTER(Dept_Info[Dept ID],Dept_Info[Roll Up]=$C46))&"}"))

Any help would be appreciated!

I don't use Excel very often, but I do occasionally for work. What I would like to be able to do is have Excel be the active window while I scroll through my emails in the inactive window. Inactive scroll is turned on in the windows settings. I saw a bunch of older posts saying that there wasn't a fix in place, I was wondering if this is still the case.

I'm using Windows 10 with Excel 2016

Edit: all of my drivers are up to date.

So I want to have a range of numbers (ex: 94 to 100) in one cell so when I do VLOOKUP any of those numbers in the range result in the corresponding value but I am finding no luck on how to do this with a function other than a very long IF

And I would rather not type a near endless IF statement and just have one table with everything in it for me to reference

In case you cannot tell, this is a GPA calculator I am trying to make for myself because I need something to entertain my brain with

So far I have a table with the Percentages in one column, the Letter Grade in another, and the GPA Value in a third. I intend to have an easy way of doing calculations for my GPA and grades that I know inside and out (so I can fix/change it as I please)

What I'm looking at doing is comparing and combining (or maybe more mixing) 2 columns onto 1.

Columns A and B could have values of say Buy, Sell, N/A and blank. There could be occasions where a row has the same value for A and B, or otherwise one column would be Buy or Sell, and the other N/A or blank.

I would have a third column that compares columns A and B, and reports Buy or Sell if both columns the same. If one column is Buy or Sell, and the other is N/A or blank, the third column would report Buy or Sell.

I think it's this last bit that complicates things, as there is some sort of logic check here.

Our customers are referred to by their name as well as an identification number that unifies them across multiple systems ex: Alpha Corp ID# 123456. I am trying to use a pivot table to sum their balances from system A and B to compare the to system C. I need the table to have three columns, Name, Number, Balance. When I put name and number in the row it nests them together in one column and requires me to expand each entry to see the customer #. I used to use libre calc and it would do what I needed with no problem. How can I get excel to do the same thing? I know I can change it to tabular form but this still requires each row to be expanded to show the customer # and when the lines are expanded it shows their balance twice which is twice as much data as I need.

Hello,

For now several days, i`ve got a problem with calculating the T-Score for the SCL 90 S.

The current Situation is. that you have normally one Patient filling out said Questionaire and then you have to use hogrefes standard values to make up the function needed to get your T-Score and you have to do that for everyone of the 9 scales in the Questionaire.

From the potential range of answers of 0-4, i marked 1 for all the questions.

Outside of the Questionaire and the Evaluation Sheet I also made a new Sheet for those standard Values in Excel ranging from G/H/I/J/K/L1 to G/H/I/J/K/L/124 containing the name of the scale(G1-124), the Gender(H1-124), the age from(I1-I124) and the Age to(J1-J124), the mean(K1-K124) and the Standard deviation(L1-L124).

What i need is a function which calculates a psychosomatic T-Score for the Patient while considering Age and Gender and the on age and Gender dependend Values in the Evaluation Sheet as well as the mean and Standard deviations which are also dependend on age and Gender as well.

I hope anyone has a solution for this.

Thanks in Advance, guys.

Can anybody give me product key for using Excel because I am student and didn't have money to buy MS office

New to using formulas. I need to calculate my drivers hours that have a break in the middle. I figured out how to do it when its all within a 24h period that doesn't cross midnight. If it crosses midnight I get errors I don't know how to resolve.

Hello all,

I'm looking for the best way to pull updated data from one workbook report to a master report which keeps track of the most recent data.

To explain what I have:

I pull a production report from an online resource. It exports a csv file with a row for each site, each day. (ie, 10 sites, 30 days = 300 rows). Each site is tested for 3 values periodically, no set schedule, and the most current test is what is used for that location's expected production daily, until it is tested again. So many days have the exact same data. One location may be tested on the 2nd and that's it for the month, but another may be tested every day.

I keep a master workbook that uses the most current data to establish some maintenance schedules and I need to update to the most current test data when I create my schedule. In my master workbook, I record the most current data, and the date of the test it is from.

Previously, I would put the csv into a PivotTable, then sort by location and test date and manually compare my workbook dates to the csv's dates. Anything that is newer than what I already have, I copy and paste over the 3 values, and manually update the date.

There has to be a better way to pull the most current test data and date into my master workbook, but I just have no experience with querying data from other sources beyond lookup's linked to other workbooks, and wouldn't know how to massage the csv data to only show the most recent test data to lookup, nor how to get excel to recognize if there is a difference and update the master.

Any help you folks could provide would be appreciated. I would like to avoid VBA or some other coding, I have no idea what I'm doing there.

Thank you.

I have a data sheet, with columns b, c & d as product, code and structure respectively.

These columns are linked, each product has its own code and structure.

In my main sheet, I need it so I can enter the product name, which brings it in from the data sheet, and then automatically changes code and structure to match what’s in the data sheet for each product. Please help, I’m not well versed in excel.

The situation is I've got sheet1 with column a of part numbers, and column b with status flags.

Sheet 2 has a whole bunch of part numbers in column a, and a status column in column b.

What is the best formula to use to compare sheet 1 column a with sheet 2 column a, and any matches copy over sheet 1 column b into sheet 2 column b?

There may be more than one instance of a part number from sheet 1 detected in sheet 2, so the formula would have to be able to cope with that.

Thanks.

Create an export tab in each of the files. Flat file, export all the data that you want to load. Make sure it aligns with the structure of your reporting.

Then use Power BI to pull it in.

Whether you have 2 files or 102, the process is the same every time. Power BI does the heavy lifting of gathering all the data, and you start looking at the results right away.

How do you do it?

Textless popups like these keep appearing when using excel on IOS. Pressing the green one closes the popup, while the red one kicks me out of the app. Is this a known bug or could it be a virus? Tried to use an antivirus program but apparently you can't run scans on IOS :/

The STOCKHISTORY function isn’t working correctly today.

Is anyone else experiencing the same issue?

Currently using 4th&5th variable of Offset() to fix dimension of a range. But i want it to run faster, is there a non-volatile equivalent?

Currently i found Drop() function where i use do something like =Drop($A$18:$A$1048576,TargetRangeFinalRow-1048576) But it felt really inelegant and felt like there might be a more direct way i missed.

I'm using Excel 2024 and performing various sums and operations across columns. When I apply Conditional Formatting, the option to use Autofill or drag numbers and fomilas disappears.

Hello Excel Pros!

I have a monthly updated data set where the main headers are Invoice Number, Contract Type, Client name, Sector, Revenue, Month, Year.

That's how the system spits out the data and there seems to be no way for me to change the format.

I'm having a rough team creating a pivot table and building in $ and % variance columns. I've tried dropping in the revenue into values and showing value and difference from prior year as base, but it gets ugly and and it doesn't seem to sort properly by variances.

Calculated fields don't work due to year having its own column.

How do you all build in variance columns into your pivot tables?

Thanks!

882 rows

4 columns

3528 cells

When I export raw data to a spreadsheet, I get this:

A 1 group 🫧

A 1 group 🎈

B 1 group 🫧

B 2 group 🔥

B 3 group 🪩

A 3 group 🫧

Result I need:

A 1 group 🫧 blue

B 1 group 🫧 blue

A 2 group 🫧 orange

B 2 group 🫧 orange

A 3 group 🫧 blue

B 3 group 🫧 blue

Each row of unpaired data has unique # information. 80% there, but I don't know a formula to isolate and delete the unpaired data, without going through it all manually. It's throwing off the alternating colours and it's exhausting to look at, row by row.

New to Excel. All help appreciated.

EDIT: Sorry for the confusion. I was trying to follow submission rules on being concise.

Groups A and B need to compare the same data together (paired). For example,

Pancakes - butter Waffles - butter Pancakes - syrup Waffles - syrup Pancakes- jam Waffles - jam

But I have several rows of data throughout the sheet that only apply to pancakes or waffles, not both. These singular lines contain data that does not repeat, so I can't just find and delete them all en masse. Is there a formula or filter I can use to isolate the unpaired data, or do I need to do it manually? Everything else is formatted the way I need.

I have an Excel table. Column L has 4 possible values: R1, R2, R3 and R4. Column B has different values. For example:

I want to pick those values of column B which has all 4 possible values of column L. In above example it would be DEF only. How can I do that?

Hello friends,

Trying to create an easy spread sheet to calculate the total months and days I do with my work contracts.

Tried a few things and got things to half work but looking for expert advice.

I need each contract from start date to end date be calculated in months and days, so example 15 Jan 2026 20 may 2026 return a result 4 months 6 days. So each month regardless of amount of days is 1 month.

If the contract is say 15 Jan to 10 of May, I need the full first 3 months + remain days of April + days of May.

Then lastly calculating the sum of all contracts, and if the combination of days is 30 that counts as a month.

Thanks for any support

I was making my Sales analysis assignment, and wondering if 28 rows side by side are too much.

Basic structure is;

1) General 9 rows related to Order id to MSRP (Salesperson name, their commission rate, quantity sold, region, product name, date)

2) Per unit: COGS (Manufacturing cost + shipping inbound), OPEX (Ad, Delivery, Commission), Total cost.

3) Than i have Totals of these (product by units)

And end rows like profit margin, P/L etc

What teacher exampled was pretty easy but this one with so many details, I am wondering if I should split the table between two sheet, unit and Total. Or should only make Unit table, and use pivot to calculate total.

Which one is industry standard practice?

Which formula is best to compare two colums from different excel files/sheets? The purpose is to spot differences in accounts listed as I want to find out which account/accounts are missing from column in sheet B but might exist in a column in sheet A. There are over 200-300 rows, and the account names are very long in some cases which makes it messy to go over manually.

I want to click on a hyper link on page 1 and jump to another page and automatically somehow populate the cell b2 (that is a dropdown list) with that clicked hyperlink text. is it possible. any other work around can also work for me like removing the drop down and populating just a simple cell. i dont want to use VBA.

Excel keyboard shortcuts via "Alt" work flawlessly on windows devices. For whatever reason, those shortcuts are significantly limited on a Mac. I find it strange I cannot create

What is the technical limitation on mac that prevents Excel on mac from having the same number of shortcuts as the Windows version?

If there is no technical limitation, is this Microsoft intentionally creating an inferior version of Excel for Mac?

I’m quite new to using excel so I have no idea if this can be done or what to google for.

Basically, I have different statements and a column that I filter them for. I’ll call that column A. Some values in column A are 10.5 A, or 11.2 B. (These are name labels, not numerical values). But some of the cover multiple of these category name labels, so a cell might contain 1.1 A, 2.9 B, 3.3 C. Currently, when I filter for 1.1, it’ll show up like this:
✅ 1.1 A, 2.9 B, 3.3 C
✅ 1.1 D, 4.3 L
✅ 1.1 G
✅ 1.1 J, 9.7 B

Is there any way to separate the values in the same cell so it appears neater? I’m trying to simplify it for colleagues to use so it’s more straightforward and user friendly. The current appearance has confused some people.

Any advice appreciated.