sumproduct

  1. C

    Weighted Average

    I need to find the weighted average loan amount of all loans between and including interest rates 4.75 and 5.25. I also include the volume percentage per note rate. InterestRate LoanAmount 4.875 150500 4.875 2540575 5 187500 5.375 15200 4.875 14848784 4.375 282292 5.125 4095500...
  2. D

    Counting Unique Dates within a Date Range With One Critieria (with Photos)

    Hello All! I'm trying to create a formula that counts the number of unique dates within a date range based on the type of item. I've tried several different formulas and researched ways to do it, but each formula was so specific to the poster's specific problem, that it did not work so I...
  3. T

    Formula to return unique values based on 4 criteria

    Hello everybody, I want to count unique value with following formula, which usually works, but does not for this task. The extra add on is Data'!$AA:$AA;"*Dahl*" (I believe this is the reason it's not working) =SUM(--(FREQUENCY(IF(('Visit Data'!$AD:$AD="Distributor")*(ISNUMBER(SEARCH('Visit...
  4. E

    Sumproduct ignore blank cells

    Hello Guys, I m trying to make sumproduct formula working and i m stuck with it because it seems that it count as well my empty cells. I have data in column A and column B and i m trying to compare both columns and count when value from column A = column B. This is the formula...
  5. T

    Count unique values based on two conditions

    I am looking for a way to count unique values in column E based on column M = "Installer/contractor" AND column O = {"A1";"A2";"A3";"B1"B2"} Currently my formula is: (it only includes column M) =SUM(--(FREQUENCY(IF('Visit Data'!$M:$M="Installer/Contractor";MATCH('Visit Data'!$E:$E;'Visit...
  6. F

    HOW TO USE SUMPRODUCT, SUMIFS AND INDIRECT TOGETHER

    I have 12 sheets that needs to be summarises based on months and withdrawals and item codes. so i created a range name and named it Donors I then proceeded to use this formulae. however, the answer ends up being #ref! I8:I200 has the withdrawals, D8:D200 has the months, Table13[[#Headers],[Jan]]...
  7. N

    Help using sumproduct to total data from two tables

    Hello, I am having trouble figuring out a formula that needs to determine how many units of a "sub part" are needed to assemble a "final product" for a foretasted period of time using data from two tables. Both tables are coming from a pivot table, so they will be dynamic and from what I...
  8. M

    IF & SUMPRODUCT

    Hello! I am using this SUM PRODUCT formula that is working beautifully for me: =SUMPRODUCT(--($B13:$B26="DISPLAY"),$D13:$D26,U13:U26) However, I now have to add some criteria that says 'IF the outcome of the above is >(3%*U$29), then just put (3%*U$29) (ie 3% is the max). Unfortunately I...
  9. F

    SUMPRODUCT IF value equals

    Hello, could you please kindly help me with the below, to make it so that it'd only consider values for the calculation if the corresponding value in column 'A' = "XX", and instead of specifying the range, sums up all the matched values in a given column? Please also note that values start in...
  10. M

    [google sheets] - SUMPRODUCT within ARRAYFORMULA

    Dear Demigods, Hope that I can post this here..... I'm using Google Sheets and I have the following formula in column O which is copied down as far as the eye can see: =ARRAYFORMULA(SUMPRODUCT((GOODSOUT!$A$2:GOODSOUT!$A$1004=GOODSIN!C2)*(GOODSOUT!$L$2:GOODSOUT!$L$1004))) Google Sheets has a...
  11. M

    =SumProduct Funtion #Value Error...

    Hello community. I'm sending attached the file that's an adaptation of a free template that i found online. During my adaptations i came across with a problem that i can't solve: In the attachment you're allowed to see the formulas apllied to "NJ" and "NK" cells, where in the formula bar the...
  12. M

    Passing an integer variable into VBA evaluate SumProduct

    I'm trying to pass a variable into my evaluate statement and I can't get the syntax correct. Let me know if you can help This works: Labor = Evaluate("=sumproduct(--(g10:g50000="Forecast"), --(j10:J50000="CWIP"), --(y10:y50000=2020), --(z10:z50000=4),(aq10:aq50000))") but I want to change the...
  13. L

    SUMIFs, SUMPRODUCT, and INDIRECT

    Good Day, I am trying to solve this issue, but I didn't find any mistake in the formula. Could someone please help me check or correct the formula? Thank you You can check the file in this link: TEST FORMULA.xlsx 😁😁😁
  14. V

    SUMPRODUCT of MAX

    ABCDEFGHIJ10123456210.5300300300300450600750900300.252000501001502002503004011000100200300400500600510.25600600600600600600750900610.3100100100100100120150180710001150130016001920240028808901300101112123134145D2:J6D2=SUM(MAX($A2, ($B2*D$1))*$C2)D7:J7D7=SUM(D2:D6)B9B9=SUM(MAX($A$2:$A$6...
  15. D

    Excel Formula Help

    Afternoon All I've cobbled together the following formula to give a Subtotal with multiple options when a column is filtered but it seems to be counting some things twice. I'm sure there is a far more elegant formula I could use but the one I am using is below...
  16. P

    Finding the the first non-zero and last non-zero in range

    Hello I have a dataset as follows and to the right (in bold), the desired outcome: I'm trying to return the the first and last non zero and return the year in the first row. Much appreciated 2021 2022 2023 2024 2025 2026 first non zero last non zero 0 0 1 1 1 0 2023 2025 1 1 1 1 1 1...
  17. A

    Index & Match with Sumproduct for repeated values

    Dear All, I have the following data list with two main fields (Category and Country) for Year 1 and Year 2, that I want to get indexed in order from max to min, adding up the values for those categories repeated in the below table. I would like to have i.e. For Year 1 and Country F the list...
  18. L

    Make =Sumproduct ignore blank cells instead of returning "#VALUE!"

    DEFGHIJKLMN21 varm1 varm1 varm1 varm1 kold1 varm#VALUE!#VALUE!#VALUE!31 varm1 varm1 varm1 varm00040260,00 kr.42 kold2 kold2 kold0001 varm16455,00 kr.51 varm1 varm1 varm1 varm1 varm1 varm1 varm70455,00...
  19. M

    Sumproduct across multiple sheets or rows

    Hi all, First time posting. Having a nightmare with "Sumproduct" I've created a spreadsheet for my company that monitors annual leave and sickness. I have a total number of sickness days, as well as a sumproduct formula to work out occasions of sickness-...
  20. A

    Sumifs with Subtotal

    Hello Sirs Hope you are doing great, I am stuck with a unique requirement where I want to see totals of filtered cells based on multiple criteria. I want to see total of for instant Group U10 for Paid, Pending+Empty Cells in the Cells D25 & D26. Total of Paid should be total entries of Paid...

Some videos you may like

This Week's Hot Topics

Top