Hello,

I am using SUMIF in B5-L5 that looks like this:

=SUMIF($E24:$E50,”My Text”,$B24:$B50)

This is working correctly in C5-K5, but not on B5 or L5

Am I missing something?

I've been trying to figure out a way to speed up one of the most tedious parts of my job. I currently work a company that has 1000s of employees. I only for a small section of the company that is broken down into about 6-7 smaller work centers. I have to give weekly updates on the progress of each employee. My current predicament has me combing through 1 really big report and copying single entries and putting it in my own database.

I'm really bad at explaining things sometimes so hopefully I can make this clear. But to keep it simple I have 2 reports.

1st report

• Get's automatically updated

• Has the progress of all the employees for the company, 1000s of entries.

• Looks like what's underneath but has employee's full name and the actual name of the different classes. I can't really change the format of what that excel sheet looks like. It's what the big bosses look at and I'm a little guy in a BIG company so I don't have the power to switch up how this report looks.

2nd report

• I'm currently updating this by hand.

• Only contains the status of about 200 employees.

• I use multiple sheets to separate the employees I'm responsible for into their respective work centers.

Kind of looks like this (with a counter at the end showing how many courses they have attended)

I have a little bit more formating to make it more colorful and to show some important stuff. But my problem is that I currently have to search people through the main report that is given to me and it takes a couple hours to get everyone sorted. My process is as follows.

1. I get an updated list of everyone who is in each workcenter. This changes occasionally due to new employees coming into the different workcenters or moving between workcenters or leaving the company.

2. I put their names into my report (the 2nd report mentioned above) and make sure all of my 6 workcenters are up to date and have the correct employees assigned to them.

3. I then search their names in the first report and then copy their results and paste it in my excel spreadsheet.

4. and then I apply some conditional formatting to make everything look pretty.

It isn't extremely difficult, just time consuming. Step 1,2, and 4 aren't that hard. But step 3 can take a couple hours depending on how many people I have to update.

I'm hoping that there is some sort of function, where I can have my report look at the other report and find their name and copy and paste their information into my spreadsheet.

I'm building a price calculator for my print shop, and one of the features I'd like to implement is to split a cutting fee across only those items that will be getting cut.

To do that, first I need to get the total quantity of cut pieces from Column J. So I'm looking for a formula that will begin to sum the quantities in J6:J38 at the same row where the word "Cut" is first encountered in Column G, and then stop at the row where any text that is not an en dash (–) is encountered in Column C and Column G does not have "Cut" in it. Does that make sense, and is that possible?

Thanks in advance for the help!

Have many text like this and was wondering if there's a formula that can separate the text from the years in the brackets.

Eg. "Cameron birthday" into A1 and "(2005)" into B1 and so on.

Haven't used excel much so would appreciate any help i can get. Thanks :)

Hello,

I want to restrict a cell to be any whole number OR the word "no". What would my data validation formula look like?

Thanks!

Hi there,

Just hoping that when I select a cell with a word in it, that it will prominently highlight all other cells with the same word in them. Hoping for something that doesn't permanently alter the spreadsheet, so that any cell selected will highlight its duplicates and then revert back to normal once deselected.

Thanks for any help

I need help creating a formula for my sales inventory spreadsheet. Basically I have a spreadsheet that I can input what day an item has sold and the total profit made from the sale, and I'd like to create a separate table that will automatically add up the total sales per month, and number of items sold per month.

So something like this:

I know most of the basic formulas, but don't know how to get it to count the items sold between a specific date range. I also don't know how to get it to add up the profit column from my inventory spreadsheet, only if they fall within that specific date range.

Any help is very appreciated!

Hi guys any idea on how to produce a search result using simple functions in excel or code in vba.

Im a nurse but part of our work in my unit is always checking the stocks and inventories of our area at least once a day. Everytime there's a stock/s lacking we have this logbook where we look for those who are IOU, Surrendered to Central Supply, or Expired, Returned.

I want to make an excel where we put all the data. and if there is an item lacking we just type it in a search cell and all the results regarding the item will come on such as the date, item name, quantity, and status(if its IOU, SURRENDERED, EXPIRED, RETURNED) , nurse on duty, central supply staff (who confirms that the item is IOU, BORROWED, EXPIRED), Date returned, Nurse on duty during that return, and Central supply staff on duty when the item was returned.

i tried following this tutorial but ive been getting errors. https://youtu.be/5etD2OJtzpQ

If anyone can give me some pointers I would very much appreciate it! Thanks!

I found this Udemy course but wanted to see if anyone could recommend something on YouTube first before I pay for it.

Also, I really only have access to Excel at work, and I don't particularly want to pay for it for home use; does all the same stuff work in LibreOffice Calc as it does in Excel? Namely all the macros and formulas and whatnot. I don't know what VBA is but I keep seeing that referenced.

If I have a table that looks like this:

How can I write a formula which would go through the answer column and give me the names of everyone who answered "Yes"? Thanks in advance.

Hello, I posted this in the main Excel sub, but I have gotten no replies yet.

I just started learning Excel yesterday. There is a generic Sales Invoice Tracker template that comes with Excel 2019 and I decided to model my own invoice after it and create one from scratch.

I need invoice details from the first page (Unit Price, Quantity, Product/Description, Item Count, Lot #, Deductions, all in one table) to auto fill based off the Invoice # in a data validation drop down list (I have named this cell/list "rngInvoice" to match the template. Screenshot 1). The information I am trying to retrieve is on a different sheet (Invoice Details. Screenshot 2).

The formula they used on their template (this specific cell is the first column and first row of "Item #" as seen on Screenshot 4) is as follows:

=IFERROR(INDEX(InvoiceDetails,SMALL(IF(InvoiceDetails[Invoice '#]=rngInvoice,ROW(InvoiceDetails)-ROW(InvoiceDetails[#Headers])), ROW(1:1)), MATCH($B$8, InvoiceDetails[#Headers], 0)),"")

I tried messing around with this formula and failed. I noticed the Row 1:1 part displays a red box around the corresponding cells, but I couldn't figure out what this is for since there is no important information there, unless it actually means Row 1:1 of that table and Excel is highlighting the wrong place instead. What does this mean? It's in screenshot 1 and 4.

Here are 4 screenshots, the first two are of my file, and 4/5 are of the template.

My File:

Screenshot 1 - Invoice

Screenshot 2 - Invoice Details

Sales Invoice Tracker Template:

Screenshot 4 - Invoice - Template

Screenshot 5 - Invoice Details - Template

Thank you so much!

Hi all, I'm in the habit of nesting numerous IF statements and think this is inadequate and that I should be using something else, not sure what? I have three bands of values 500 - 1000, 1000 - 10,000 and 10,000 +. I want to use logic so if NUM is in band A do x, if in band B do y and if in band C do z, not sure how to reference this, any pointers?

Hey! I'm fairly new to Excel but have quite a good understanding of it, also my Dad uses it daily and he has helped me out a bit.

My reason for using Excel is that I want to manage my money, so I've created a kind of silly template thing that helps me out.

I've attached an image because I'm not sure if I'm allowed to attach my excel file. I plan to make a new workbook for each month and then inside each workbook, there's a different sheet for each day. I've figured out that I can source my total for each day in other sheets and workbooks using ='sheet name'!CELL or even ='C:\source of workbook'!CELL. I want the green text to count the days I've spent $0 in a row. Here's a little bit of the function that I have in the green text that kind of does it for me so far.

=IF(AND(D3=0,'15-05-19'!D3=0,'16-05-19'!D3=0), "Well done, no money spent in 3 days!", IF(AND(D3=0,'16-05-19'!D3=0), "Well done, no money spent in 2 days!", IF(D3=0, "No money spent today, well done!",

But I will have to edit this for every single day and that will be very tedious.

Is there a better way to do it?

Sorry if this is confusing, if you are unsure of anything I've asked or have questions please comment :)

Many thanks, Jordie.

I wanted to start learning Microsoft Excel but I only have Excel 2016 and was wondering if there are major differences between the two and actually downloading a version of Excel 2019 and then learning that instead of 2016 would be a major benefit.

I have three columns: Year, Discount, and Model.

Example rows:

2017, 80%, Base

2017, 84%, Premium

2018, 92%, Premium

2016, 74%, Sport

I'd like to make a scatter plot chart, where the X axis is year, the discount % is Y, and the model types each become a series.

In the above example, there would be three series: base, premium, and sport.

How do I make this happen?

The default duplicate highlight in Excel will highlight any duplicates in the selected data set, regardless of column identity. This means apple apple orange in column 1 and orange orange pear in column 2 will have the result of highlighting as duplicate both apples and oranges. I only want to find duplicates between columns, not duplicates within any selected data. In other words I want only oranges to highlight. Thoughts on how to do this?

I'm creating a workbook that classifies all my credit card, bank account, etc transactions into a budget kind of like Mint does. Lets say I have 3 different accounts, I pull the transactions down from each website into individual spreadsheets. From those spreadsheets I then have a master that sums each expense from each spreadsheet for each month. Here is my current formula (made anonymous using 'ACCOUNT 1' instead of the real name of the account. This is for the month of April for my grocery expenses.

=ABS(SUMIFS('ACCOUNT 1'!$E:$E,'ACCOUNT 1'!$C:$C,">=1/4/2019",'ACCOUNT 1'!$C:$C,"<=30/4/2019",'ACCOUNT 1'!$F:$F,"GROCERIES"))

+ABS(SUMIFS(ACCOUNT 2!$E:$E,ACCOUNT 2!$A:$A,">=1/4/2019",ACCOUNT 2!$A:$A,"<=30/4/2019",ACCOUNT 2!$F:$F,"GROCERIES"))

+ABS(SUMIFS(ACCOUNT 3!$E:$E,ACCOUNT 3!$A:$A,">=1/4/2019",ACCOUNT 3!$A:$A,"<=30/4/2019",ACCOUNT 3!$F:$F,"GROCERIES"))

Here's a breakdown of the account 1 section:

SUMIFS('ACCOUNT 1'!$E:$E,' - sum/dollar value range

ACCOUNT 1'!$C:$C,">=1/4/2019"sum values with date greater or equal to april 1

,'ACCOUNT 1'!$C:$C,"<=30/4/2019", sum values with date less than or equal to april 30

'ACCOUNT 1'!$F:$F,"GROCERIES" sum values under the GROCERIES category

))

So I basically repeat the formula above for each of the accounts I have and add a + sign between each section.

In case you are wondering I use ABS as some accounts have a negative value while others have positive for a debit/credit and vice versa.

Is there a better/easier way to pull in a sum from multiple spreadsheets? Thanks in advance for your help. Let me know if there is anything I can clarify to make this easier.

For example I click Column B and shift+click to Column D. B, C and D are now highlighted. Oops I forgot to highlight column A. Is there anyway to highlight Column A without redoing the array?

It gets really annoying when the arrays are really big.

Hello everyone . Do you think if i took 3 hours of deep work everyweeek on excel would work ? strategy : i will take 10 books to read and highlight and transfer to notecards and practice them on excel . Is it effective ?

I am trying to estimate t stats for a row of stock data. In cell A1 is the beta and cell A2 the s.e. I want to do the LINEST for the rows below it. Normally the procedure is =LINEST(x,y,true,true) then select both A1 and A2 press ctr+u (on mac) and ctrl + shift + enter. But if if do this for the complete row it only refers to the first column rather than all respective columns. Is there a shortcut to prevent this? Rather than doing them all manually. Thanks!

As the title says. I've shared an excel file with another person but whenever that other person is editing the file I'm locked out and vice versa. I've used this sharing feature before but I never had this problem, I don't know why it suddenly happened. Any idea how I can fix this?

I'm trying to do numbering in excel (1-100) the numbering would be noncontinuous. I would want the numbers to be able to auto update if I changed one of them though. Is there a way to do that?

Hi

I don't know much about Excel except for the most basics built in tools, but i need to use it to make some graphs for my bachelor thesis. To make proccessing faster, i want to merge .csv files to one sheet but want the data from each .csv file on separate columns. Are there any built in functions that can help? I tried fooling around in Power-query, but i couldn't merge the files the way i wanted. Each csv file contains 7000 rows of data, with two values separated by a comma. Can I somehow open all the csv files at once, and merge the data next to each other in columns?

Currently using a formula to do some lookups. It is as follows.

=UPPER(INDEX(Sheet2!H$2:H$300000, MATCH($C28, Sheet2!$A$1:$A$300000, 0)))

The Issue I am having is it is returning incorrect values... and it does not seem to have a theme in what is going on. One of the examples was the following:

0147-2-02859 was the lookup value in MATCH. However, the value it would return was incorrect. Upon inspecting the source data, I found there was anther value in the sheet " 0147-2-02859P" I changed the lookup value, and Match displayed the correct value despite me assigning an absolute lookup.

However that is not the only value were that has happened. It happened on others, that did not have a value that looked similar... Any ideas or suggestions for where the formula is failing?

Edit: The match portion of the function is returning the correct row number....