sumproduct

I need to apply a minimum price to a sumproduct formula. If I multiply Qty x Price and the amount is less than £100 then a minimum price of £100 applies. I need this to be a solution with a sumproduct formula as I need to perform other calculations in the same cell with the result. Any help...

Dear users, I am trying to do the following but have been unsuccessful therefore I seek your help; I have vendors who buy TVs from certain Towns. The top left table displays the quantities of TVs bought by each vendor who has a record of purchase while the bottom left table displays the...

Hello Experts, i need to plan my purchase requirements , we have a multilevel Bill of materials & few components are manufactured inhouse , few are from external vendors , our vendor base is also global.. few components are common for more than 1 finished goods.. some components are required to...

Hi! I have been working on implementing MRP Excel solution in a company I work for. I have already done one, and now I was working on a new more detailed system. My company prohibits me to install XLB2BB add-in so I will do my best to explain everything. First sheet 'ProdBOMs' contains Bill of...

Hi, I have a SUMPRODUCT and IF formula which works fine together but I am trying to merge this with an INDIRECT formula: =SUMPRODUCT(IF('No. 1'!$A$1:$A$500=E$4,'No.1'$E$1:$E$500*'No.1'$F$1:$F$500)) No.1 should be the contents of Cell A8, A9 etc. I've tried...

Which formula of sum product should I put in the column C cells to calculate the cost basis of shares, which ignores the empty cells in column B and so considers only the prices in column A by which the quantity in column B is entered? I tried to use the following formula, but it gives me the...

My question is in the image below which relates to a complex way to sum columns according to a number of criteria. I hope someone might be able to help

I am self taught and don't always know the right way to ask the questions to google. 2 days ago I posted my sumproduct formula and with help it worked perfectly. Now I am trying to add an IF for a zero return, (which is ok, I need the zeros to populate), but if it is not a zero, I need it to be...

Hi there, looking for some assistance in how to achieve a result without the added step. I have it in my head I can use Sumproduct but I may be wrong. Any assistance would be appreciated. Apologies, I'm on a works laptop and I don't have the ability to download/install the plugin or even attach...

I am trying to find if same id numbers has an overlap in dates and I got the following. Can you please tell me where I went wrong?

Hi, I have asked this question on MS website ; Redirecting If I have a an array from indirect ; INDIRECT({"Sheet1","Sheet2","Sheet3"}&"!"&"A1",TRUE) , which returns a spill of "#VALUES" but if F9'd will show ; = {2,3,4} , which is...

So I have a lot of data and someone has changed how it is imported! I needed to find how many different orders were taken by a salesperson in the month so ran this COUNTIFS(Dels!$O:$O,"BOB",Dels!$C:$C,">=01/08/2023",Dels!$C:$C,"<=31/08/2023",Dels!$H:$H,">0.00") which ran perfectly as all...

Is there a way I can shorten this formula. Works - This adds "US", "VI", "PR", and excludes "CA"...

Hi there, I have two tables for labor rates by function and by year. One table for GBP currency, and one for USD currency. I then have a table that contains labor hour requirement by contract (many rows). Each contract is either native to GBP or USD. I'm trying to use the following formula to...

Hi I am trying to find Table1[ID] that matches with Table7[Name], "Singer" The only way to find this is if Table1[ID] = Table7[IDs]) and Table7[IDs] = Table7[Name], "Singer" but it keep showing error when I try to count it, how do I go around this?

Not even sure where to start. The formula shown is supposed to read the various grades of material in P,Q,R,S column and then find which grade bin it sits in from the reference table and return the BIN type from Row 5. This used to work in an old version of the sheet I have but I'm trying to...

I have a database that shows a bunch of courses required by my employees and then a column for every week in the year to track at which point the employees attend the course and get credit for it. At any given week I want to know the percentage of students that graduated from class as an...

I'm trying to build a formula in my rates tab (see below) to show the minimum price of the unit found. I've started trying to build the SUMPRODUCT statement but getting a value error (see rates tab below formula bar). I noticed the dates on the comps tab were string rather than date so I added...

Hi all, I work for a finance company, I receive a large data set via a webhook from a cloud software provider. We use the dataset to do client reporting. The dataset has all of the investments each client has. There is 217 rows, across 15 different clients. When we present the information to...

Hello I'm hoping you can help if possible please. I have a spreadsheet with 3 tabs, on tab 1 is the formulas / calculations, tab 2 holds all the data and tab 3 has dates (start/end date). I am trying to calculate how many occasions of absence an employee has within a 6 month period and...