1. A


    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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. T


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


    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...
  10. 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...
  11. 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?
  12. 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...
  13. 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...
  14. 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"...
  15. 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...
  16. 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?
  17. 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...
  18. A

    Need minimum value in a SUMPRODUCT formula

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

    SUMPRODUCT using vertical and horizontal matches

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

    Alternative to complex SumProduct to rank data by specific variable

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

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