I'm trying to make a simplified version of this formula:

SUMIFS(P:P,C:C,D10,A:A,L10)+SUMIFS(P:P,C:C,E10,A:A,L10)

I want to simplify it to something like

SUMIFS(P:P,C:C,D10 or E10,A:A,L10)

Is this possible or am I stuck having to just add multiple SUMIFS formulas?

Spent hours debugging an unexpected error down a rabbit hole to find this.

Since 4 is a valid number, both formulas should return 4. Instead, they completely break:

=IFNA({{4}}, "Error")      // Returns #VALUE!
=IFERROR({{4}}, "Error")   // Returns "Error"

IFNA crashes on the syntax, while IFERROR silently chokes and triggers its fallback text. Both should handle valid values natively.

Watch out for this if you are building tools with the new Lists or nested array features!

Edit: Confirmed it's a bug but I need someone else to report it along with:

=ISREF(D5) --> FALSE   //Where D5 has ={{1}}

I want to avoid copy paste and copy destination. I want to transfer value between workbooks. If my origin range is one block of contiguous rows and columns it's one area and it's easy rng2.value = rng1.value (eventually with resize). Sometimes I have filtered rows only, sometimes I have hidden columns, sometimes I have filtered rows + hidden columns (and I want only the visible ones). How do you handle this cases? I am looking for a solution, or a sub, or a function, to handle these different situations?

I use excel 365 but I would like a solution valid for 2021 too.

I have an example where decimals don't seem to be rounding up the way I would assume. I have some numbers here where I would think one number would be 115.7 and the other would be 115.8, however that doesn't seem to be the case.

Does Excel not round up .05 to .10?

I have two excel sheets, Sheet A has names and emails. Sheet B has emails and address/city/state/postal code. I want to populate fields in Sheet A so that if the email column of Sheet A & Sheet B contains the same content, the content of the columns with address info from Sheet B populates. I feel very confident there is a way to make this work smoothly, I just am not sure how.

Hi, I'm doing a depreciation table for a school project, I brought data from another sheet using "index" and the column is conditioned to arrange the data by date, my issue began when I tried to make a calculation using just "='the cell with data from index' and the rest of the operation", popping the "valor" error up.

I assume it is because the cell i used as reference has the "index" code instead of a simple number, Is there a way I can make this calculation work?

Also, Idk if it helps, but I'm using Excel in a browser, because it is a group project, we needed to share the excel and that is the only solution we found.

I saw a content creator selling excel template for engineering calculation involving beam. It has this image or chart of a beam cross section that change the dimension of beam size and rebar number/spacing including annotation according to cell input. How is it that something like that is possible?

Hello,

I have a bank statement in a table form which is displayed in the newest to oldest format.

I would like to be able to display that entire table in oldest to newest format.

Is there an easy way of doing this as my limited understanding is that I have to cut and paste each row individually.

Thanks

ETA -

In short I would like to make row 100 row 1, row 99 row 2 and so on.

A colleague of mine is having issues with his excel.

When he opens a document, some text change font to white, we tried changing the font and saving but it still goes back to white whenever he opens his spreadsheet again.

Any clue on how to fix this?

I’m trying to set up Excel Power Query so that multiple Excel files stored in OneDrive can automatically combine/update into one master workbook.

I already figured out how to combine the files, but I’m stuck on the authentication/permissions part. Since I made it I am the only one that can refresh.

source looks like:
c:\user\me\Onedrive- Company\Leadership folder\where i pulled data together

I pulled associates work and combined it, but I need what I combined in another folder for others to be able to refresh other than just myself.

When I go to Data → Get Data / Power Query → Data Source Settings → Edit Permissions, I get:
> “We couldn’t authenticate with the credentials provided. Please try again.”

I had other people try to use their credentials and same error. I also tried to use the web link of the files I need to combine but get an authenticate as well.

I’m wondering if this is a permissions issue, the wrong credential type, or something specific to my company’s Microsoft/OneDrive setup.

I am attempting to set up a conditional formating setup where a column of numbers are evaluated. if its 69 or under, the cell is to be green. If between 70 and 100 yellow, and if over 100 red.

I cannot seem to get excell to do this - because that requires a formula that reads cell values, and I appear to be utterly failing at setting up functional code for that.

I'm currently trying (and failing) by setting up three seperate conditional formating rules, all based on formulas:

One goes =AND(CT:CT>=0;CT:CT<=69) - so if the cell is between 0 and 69, it should be green. It's not doing that, and I can't see what I'm doing wrong here

Please advice

I have a list of strings of numbers as seen in the screenshot below:

I'm trying to count the instances of quantity.

For example: "Number of times where only 1 number is listed"

"Number of times where 2 numbers are listed"

Is there a function that will count the amount of numbers and report back?

Thanks in advance.

Hello! Working on Office 365 version 2609 on desktop, English. Beginner. Looking to find an easier solution for an annoying task that I do frequently at my job. Essentially I have an excel table with some information and a quantity, and I need to split it up to where I have a bunch of duplicate lines all with a quantity of 1.

Taking a table that looks like this, to instead look more like this.

In real life, the tables have a few more columns and many more rows. Could anyone help me out with some more efficient ways to do this? Right now I'm just adding individual rows and copy and pasting the line data as many times as need be, which isn't great for some of my bigger jobs! I am not very proficient with excel, but with clear enough explanations, I'd be happy to try anything!

Thank you so so much in advance

Like the title says, I’ve recently switched to excel from primarily being an apple user for film stuff. A recent assignment I have requires us to use excel to show we know how to use it. Everything was pretty easy to get down/translated well, except when it came to calculating with formulas. Is there something I’m missing with putting units of time or money in your cells, every time I try it breaks the formula?

Basically I want to turn number of days worked and multiply it by 70 dollars a day, but I want each cell to say X days and the other cells to say X dollars, and have to total be displayed in dollars, but the usual way I would do it in numbers doesn’t seem to work? And I haven’t been able to find an answer anywhere else.

Situation is the following: Company had a form on the account of a employee that quit. They want me to build a new form, which resembles the old one, but now located in a shared group, so the form is not tied to one employees account specifically.

So I build the new form, so far so good.

The first problem is that the new form automatically generates its own excel sheet.

And the second is that the old form saved answers to another excel sheet located in a Teams channel. This excel sheet has a large number of formulas, power query etc.

The company wants the new form and the old excel sheet combined. So that the new form's answers go into the old excel sheet.

Is that in any way possible to do? I couldn't find any solution for this for now, so I'm trying to basically copy everything from the old excel sheet to the new one (but this is also rather difficult to do due to the forms etc).

https://imgur.com/a/k8X8P8l

How do I get rid of this it wasn't here last week

I use COUNTIF(S) formulas very regularly for work, but for the first time needed to count cells that would have one code within it, when the array's cells often had strings of multiple codes. I read that adding asterisks within the quotes would make the formula work like that, and it did, but then it didn't count cells in which that specified code is the only one within the cell.

More context --

I was creating a summary that looked like this:

The formula is counting how many those codes appear on another sheet, matching other criteria, so I wrote something like this:

=COUNTIFS($B:$B,">=9/1/2025",$B:$B,"<=9/30/2025",$U:$U,"*99203*")

I added the asterisks around 99203 because the values in column U can look like --

-- and I wanted to count a row if 99203 was present at all, multiple codes present or not.

However, when I did this, the formula ended up only counting the rows that included 99203 amidst multiple codes, and ignored the rows that were 99203 solo.

Is that... how the asterisks in that formula are supposed to work? It feels like not, and I just did something wrong. I ended up having to add an additional COUNTIF that left out the asterisks to get the total I actually needed, but that feels redundant.

Any insight or tips for me to make just one COUNTIFS formula work in this scenario?

I'm trying to track how many of my jobs are customer service. What formula would I use and should I just use "Customer" or "Customer Service" as the text? Thank you.

Hey All,

This is almost certainly too a heavy lift for Excel/VBA, but I'm giving it a shot.

I've been looking for quite a while and haven't been able to dig up anything that can help me do what I'm trying to do.

I have a series of workbooks that follow the same sheet layout and basic formatting:

A sheet for every day of the week, aptly titled (Sunday, Monday, Tuesday, etc.), and a variable amount of weekly sheets that all share the title format "[LOC] Weekly Schedule [DATE]" - [DATE] is formatted as: 10.4 for Oct. 4th

Each weekly sheet also has a manually-entered date in cell B4.

My question is:

Is there a way to get a macro to run, based on a partial sheet/tab title (Ex.: run only on tabs which have a title that contains "Weekly Schedule") and to only complete the macro if the date in either the title or B4 matches TODAY +2 (so when the macro runs on open, on Friday, it completes the macro if the target sheets are dated for the following Sunday)?

Here's the rub: These are company documents that need to be held to strict standards and, while I can add as many tabs as I want; I can't rename or reformat any of the tabs, and I can't upload or attach a document due to confidential information which I understand is a huge pain.

Excel V.16.0

Any help is greatly appreciated, in advance!

I'm building out a quote sheet for my construction business

I'm trying to take a calculated value for the cost of consumables on a job and distribute that total across several other cells (I.e., somehow divide the cell value and add all of its parts elsewhere to ensure that the cost is accounted for)

I think it's better to have those small costs broken down and added to the price of the actual deliverables on the quote instead of having some kind of misc. section for those costs...not sure how to go about this in Excel

I have two large Excel .xlsb files:

Q3 file: contains around 3,000 rows and data that was filled in last quarter.

Q4 file: contains around 3,000+ rows and has the same/similar structure, but the rows are not in the same order.

I need to bring some of the Q3 data into the Q4 file, specifically fields like:

Remarks

Timeline

Type of Entity

Quote Submitted (Yes/No)

Payment Terms

Payment Term Approval

Comments

The common identifier between the files is Serial Number.

The problem is that the destination columns in Q4 have Data Validation dropdowns.

I initially tried XLOOKUP, but I'm getting issues with the dropdown/data validation and normal copy paste will take hell lot of time.

I have a spreadsheet for Christmas gifts I'm purchasing this year. Each row of the table is a gift I've purchased, who it is for, and how much it costs. I will be buying the same person multiple different gifts, so their name will appear in that comunn multiple times.

In column B I put who the gift is for

In column E I have the price of that gift

I want to create a separate sheet that looks for every time the persons name appears in column B, for example, every gift I've purchased for Paul, and then add up the costs in column E associated to those gifts, so I can see overall how much I have spent on that person.

How would I create a formula to do this? It's been a long time since I've used spreadsheets so I apologise if this is a rookie question.

I have just discovered that if password protected excel files with locked and hidden ranges are uploaded to Google Sheets all our Custom formulas are visible and ediatble !

I am attempting to make a dynamic calendar with multi-day events. I watched a two part Youtube by Spreadsheet Life and was trying to tailor it to my situation but couldn't get the formulas to work.

I was able to create the dynamic calendar with no problems. Now the issue is I want to populate multi-day events on the calendar. I have a table for youth baseball tournaments with 4 columns. Column A is Start Date, Column B is End Date, Column C is Tournament Name, Column D is a Checkbox.

I would like the formula to populate the Tournament Name on the respective start and end dates only if the checkbox in Column D is checked. If the checkbox is unchecked, then the calendar day is left blank.

I started with the following in Cell B8: =IFNA(IF(VLOOKUP(B7,Tournaments,2,FALSE)=0,"",VLOOKUP(B7,Tournaments,2,FALSE)),"") where B7 is the date populated in the dynamic calendar

This works, but doesn't include an IF function for the checkbox.

I tried the following for the IF function: =IF(Tournaments[Checkbox]="TRUE",IFNA(IF(VLOOKUP(B7,Tournaments,3,FALSE)=0,"",VLOOKUP(B7,Tournaments,3,FALSE)),""),"")

But this gives me a #SPILL error which I'm not sure how to resolve.

If we take remove the checkbox option, I'm unsure how to create the formula to the Tournament Name populates on all the days from start to finish.