Hi
I am trying to do a sumproduct as a dynamic vba code but I cannot get it to work.
I'd use XL2BB but i'm on a work computer so i can't install anything, so attaching picture for some clarity.
This is for an order form and the reason i want it dynamic is because the number of rows changes...
Hello All,
I have a formula that works as I want combining sumif and sumproduct. However, I am referencing another workbooks so I would like to use only sumproducts to make this happen. I have excel 2019 and don't have any power query functions...
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...
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...
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.