sumproduct

Hello, I have used solver to optimize the sum of numbers given certain constraints, however I am now trying to optimize the product of numbers given certain constraints. When finding the optimal sum I can use a binary list to select the combination of item to sumproduct together to find the...

Hi I have reached the edge of my knowledge and need some assistance. I have a sheet that tracks crops and input chemicals, I need to summaries this into a table so that I can budget the cost based on the month the item is consumed. In Sheet 1. Data The first few columns are for the row...

Hi everyone, I'm using a formula that works perfectly but there is just one small issue. I need to do the sumproduct of positives and negatives values. SUMPRODUCT(--(1+MOD((COLUMN(M21:CD21)-COLUMN(M21));9)=1)*(M21:CD21<0)*N(+M21:CD21)) This one is only for negatives values. So if ever I...

Hi there! I'm trying to figure out a way to sum a row based on two cells in the same row. I tried to use a sumproduct but I'm getting an error as I have text in my sum range. Any way to ignore the text to just sum the numbers? Maybe sumproduct isn't even the best way to go about this? I'm...

Hi everyone, I have a question that I've been trying to figure out and hoping the community might have some tips. For my individual stock portfolio, I'm trying to calculate a weighted average in cell D21 that excludes cell C6 and D6 from the weighted average calculation (SOFI). How can I do...

This hopefully comes through okay. I am looking to automate the information going into columns G and H. I highlighted the date ranges to make this a bit clearer. In the real world I will have "Source 2" with samples every 10 minutes. "Source 1" is just whenever a fault occurs. The goal is...

Hello, i have below data.. Customer Name Part No Sale Price Oct-23 (qty) Nov-23 (qty) Dec-23 (qty) ABC A 1.63 31200 15600 15600 ABC B 49.38 31200 15600 15600 ABC C 3.22 31200 15600 15600 XYZ A 1.63 9600 12000 0 XYZ B 49.38 9600 12000 0 XYZ C 3.22 9600 12000 0 SDF A 1.63 46800...

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"...