sumproduct

  1. P

    Google Sheets: How do you ignore text in a sumproduct formula?

    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...
  2. M

    Calculating weighted average excluding blank cells

    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...
  3. R

    vlookup, INDEX, or SUMPRODUCT???

    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...
  4. A

    Sumproduct

    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...
  5. R

    Sumproduct where minimum value applies

    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...
  6. K

    Sumproduct with 3 tables?

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

    sumproduct , data sum

    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...
  8. K

    Array SUM or SUMPRODUCT function error

    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...
  9. T

    SUMPRODUCT, IF and INDIRECT

    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...
  10. E

    How do I get sum product to ignore empty cells to calculate the cost basis of shares?

    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...
  11. R

    Sum with various criteria

    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
  12. K

    SUMPRODUCT CONTINUED

    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...
  13. S

    Sumproduct to remove second stage calculation?

    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...
  14. T

    VBA SUMPRODUCT - Date overlap when ID number is the same

    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?
  15. D

    Sumproduct versus sum with indirect

    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...
  16. M

    Unique or Sumproduct or something else?

    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...
  17. Long Nose

    sheetlist sumifs with multiple criteria - shortened?

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

    Sumproduct - Sumifs - Choose

    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...
  19. J

    Count based on multiple criteria

    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?
  20. R

    Searching Grade Bins and Returning Product

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

We've detected that you are using an adblocker.

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.
Go back
Back
Top