1. M

    SUMPRODUCT, Tiered Commissions, One for Old Customers, One for New Customers

    I am sorry if this question has been answered in the thread, Multiple Tiered Calculations, or another thread, but they are so complex, it is difficult for me to follow to know if they will help me. It seems I have a comparatively simple calculation, but it's too hard for me with my limited...
  2. G

    Sum data based on adjacent cell in another sheet but exclude column

    Hello, I have a few question about sumproduct formula here: On the Data! sheet I use [=IF(COUNTIF(C9,"*+*"),RIGHT(C9,LEN(C9)-FIND("+",SUBSTITUTE(C9,"+","",2))),"")] in D9 to extract overtime period from adjacent cell (C9) and from this point I've use...
  3. J

    SUMIF with INT

    Hi, I have this table (its spanish so decimals are commas) I want it to add items as a new total in G66 if the integer in column a is the same as the reference number in A66, but it seems it wont work on a range. =SUMIF(A14:A58;INT(A14:A58)=A66;G14:G58) Thanks for the help
  4. L

    Sum over an array based on date in row and unique identifier in column

    Hi, I am looking for a formula to put in cells B2:F7 on Sheet 2. Notes - This is a simplified version of the file I am actually working with. The file I am working with has all work-day dates (Mon-Fri) in row 2 on Sheet 1, for 01/01/2021 to 30/06/2022. - Each Trade Reference is unique, so...
  5. J


    Hi, I was hoping someone could help me find a way to avoid using an array formula for the following problem. I have two tables: Table 1: Table with hours per month, where rows are specified with department and labor category. Table 2: Table with hourly rates per month, where rows are...
  6. U

    SUMPRODUCT active link formula

    Hi guys i have sumproduct formula for counting two range of number, i want count active cells of formula that linked to another sheet...
  7. P

    Way to create rate array from total and counts (opposite of sumproduct)?

    Hi, Is there a way to calculate a rate array from a total, if one has the counts? In the example below, I've used =SUMPRODUCT using rate 1 array and enrollment count array to get the monthly total. I'm wondering if there's any way to use a given monthly total and the count array to determine...
  8. S

    Sumproduct with a dynamic moving range

    Hi Guys, I have been trying to get results from SUMPRODUCT based on the data for volumes and %s which need to change every year but so far have no success. I have attached a mini-sheet below which shows what the result should be like. Any help is much appreciated. Thanks in Advance.
  9. A


    Hi, Can anybody help me, i am trying to sum numbers base on their first numbers, i find a formula in the forum and when tried it, it gave value error because of blank cell but i tried to modified but couldn't overcome, any help will appreciated.
  10. little ribbit

    Power Query: search multiple columns against a list of values?

    I am trying to move an excel SUMPRODUCT function into Power Query. The function searches a column against a list of names in a table, then returns TRUE if one is found. Code sample: IF ( SUMPRODUCT ( ISNUMBER ( SEARCH ( Table[Column], [@[current_field]] ) ) * ROW ( $rows included in...
  11. P

    Formula to count unique values based on multiple criteria, including 1 column where at least 3 of 5 criteria should be found?

    Hello everyone, I'm not sure what kind of function of is needed here, though here's a sample of the data and what I'm trying to do: Session 1 Startup Arabic Yes Turkey Male Syrian Session 2 Startup Arabic Yes Turkey Male Syrian Session 4...
  12. J

    Changing a Sumifs to a Sumproduct

    Hi, could someone please help me convert the sumifs formula in VBA, to a sumproducts please? Thank you. ActiveCell.FormulaR1C1 = _ "=SUMIFS('Closing Stock.xlsx'!C3,'Closing Stock.xlsx'!C1,RC[-4])"
  13. B

    Counting key words in cells

    Hello Everyone, I'm currently taking descriptions and counting key words; but I'm afraid that if one or more key words are in the same cell, the formula will count each key word instead of if one meets the criteria and counts it as 1. What's a formula around this to cancel out this issue...
  14. B

    is there another alternative, IF SUMPRODUCT ?

    Hi all, Need some info on "IF SUMPRODUCT" formula. This formula help me validate my data for 1 year but this formula can slow down calculation considerably and take long time for me to report. is there something better than if sumproduct? An example of a formula is this...
  15. C


    Cant get my sumproduct indirect to work =SUMPRODUCT((INDIRECT($A$6&'[Metering Services 3 Productivity 2020 NEW.xlsx]"!$R$2:$R$100")),--MONTH((INDIRECT($A$6&'[Metering Services 3 Productivity 2020 NEW.xlsx]"!$B$2:$B$100")))=$AC$3) A6 to Name of tab B2:B100 is dates R2:R100 is the values for...
  16. L

    Can this formula be transformed to only count unique values within each sheet?

    Hello! I have learned a new formula today =SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!"&"w2:w500"),F2)) Formula source: How To Count Items Across Multiple Worksheets In Excel I want the formula to only count the criteria (F2) once in each cell. I saw this but I don't know how to combine...
  17. P

    Counting non-empty merged cells on another closed document with SUMPRODUCT

    Hello. I am trying to count non-empty cells on another closed document with SUMPRODUCT. I am using this formula: =(SUMPRODUCT(('https://link/[]Worksheetname'!$Q$9:$Q$99999>"")+0)) Everything seems fine until the array encounters merged cells and returns #REF! from those cells as...
  18. G

    Excel sumifs #value when closing excel file

    Hi everybody. I have used the formula sumifs in order to calculate the sum of values in a column ranging between, e.g., 100 and 1000, located in a different workbook. The problem is that when I close that excel workbook the formula in my first excel file doesn't work anymore and #value appears...
  19. A

    How do I exclude certain cells from a sum product formula in Excel?

    Using sum product I calculate weighted average of rate of amounts I have, as per below case I would like to exclude highlighted amount and rate from the formula. Any quick solutions? Thanks Arda
  20. S

    Subtotal on Filtered Rows using Sumproduct - Wildcard substitute

    I would love some help correcting this pickle! I need to show the total of 1 column, based on whether another column contains certain text. Looking through posts and blogs, I have gotten this far, but it will only total if BBA is the only Program in column P. It would solve my problem if I...

Watch MrExcel Video

This Week's Hot Topics

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