sumproduct arrays

  1. L

    Need VBA for SumProduct of Array and Constant raised by Array for Regression

    I am trying to create a UDF for forecasting with polynomial regression. Assistance with the final line of code will be appreciated. The code thus far is as follows: Function Forecast_PolyTest(X As Single, knownYs As Range, knownXs As Range, Order As Integer) Dim k, OrdArr, PwrArr...
  2. V

    Sumproduct Multiple criteria same column

    Pls help I want to find the Weighted average of the following data in a single column : I have tried Sumproduct(--(Heads="Freight")*(Value),(Heads="Sale")*(Value))/Sum(Sale) COUNTY Heads Value A Sale 200 A Freight 35 A Distance 50 B...
  3. K

    SUMPRODUCT or SUMIFS .What is the better approach to solve the problem

    Friends , I tried my best , but need help. Table 1 contains time sheet information . hrs ,project id, manager/resource names(2 levels only) I have been asked to get total hrs grouped by managers Managers list : Randy, Srini, Kavin Table 1 <tbody> EMP_HRS PROJECTID L1 NAME L2 NAME Type 1...
  4. S

    Sum(Countifs( ... use cell reference instead of Text in constant array .....

    I have some text in cells A3:A6. The text is: Testing1 Testing2 Testing3 I have a Sum(Countifs( formula in cell B7. The formula is: =Sum(Countifs(CriteriaRange1,{"Testing1","Testing2","Testing3"},CriteriaRange2, Criteria2, etc. etc.)) I would like to reference the cells in my array instead...
  5. K

    3D SUMIF Formula

    I am working on a project that has a sheet for each week in the year. Within each sheet is varying employee information. On the last "Total" sheet I need it to search for the ID listed in the A column in every worksheet, when found it needs to look at the L column of the same row add it together...
  6. reneev

    Sumproduct + vlookup

    I have just learned how to use the SUMPRODCT with VLOOKUP in order to sum an array of columns inside a vlookup as shown below. " =SUMPRODUCT(VLOOKUP(C6,B12:F18,{3,4,5,6},0))" Now, this particular formula is basically calculating year-to-date totals where column 3 is 1st qtr, column 4 is 2nd...
  7. C

    how to use SUMPRODUCT with 2 arrays that are different in range size

    Hello all! New to MrExcel and hoping to get answer to my question Have a monthly sales worksheet with the dates in top row and products in first column. I the sample of the sheet below is clear. What I am trying to do is figure out the use of "Type" on a daily basis. To do that I have to...
  8. V

    SumProduct Array Formula Help

    Hi, Can anybody explain me this formula please? =SUMPRODUCT(--(A2>{0;250001;500001;1000001}),(A2-{0;250001;500001;1000001}),{0;0.1;0.1;0.1}) I am not able to get complete formula. As i understood little 1st part (A2>{0;250001;500001;1000001}) explained below am i correct? <colgroup><col...
  9. N

    Tough excel problem: Lookup Array and use sumproduct on it

    I am trying to lookup a column array based on a certain criteria and then sumproduct the values in that column array with another set of values for the answer. In the sheet, there are certain players who are being scouted. Their stats (games, goals, assists, etc.) have been presented by season...
  10. W

    I would like it to go through and display all values in a column once with the totals for that category, is this even possible in excel?

    Hi I have data laid out like below, I am trying to write a formula to go through 1000's of rows in column B (Category) then display all of the records once and add up the totals e.g. CCON $123.04 COTH $456.06 I have been able to do this with...
  11. T

    Need a SUMPRODUCT/SUMIFS/IDEX/MATCH maybe array formula. Please help!!

    Hi there, I am using excel 2013 and am really struggling to come up with a solution to the following problem: I look after a spreadsheet that keeps track of the mandays of a collection of projects by week and is laid out thus: Month: Feb Feb Feb Mar...
  12. S

    Which formula can I use? Is this possible?

    Hi All, I do not know which formula to use for what I am trying to acheive, so any help would be fab... I am tyring to put a formula in sheet 'sheet 4', cell A2, that says: if in sheet 1, "further Investigation" is in column D, and a figure greater than 0 is in column O,- then show me this...
  13. D

    Count number of times rows in one column match rows in a second column between 2 dates.

    I've been searching for this for a couple days now and have come across anything. I know it can be done in VBA, but I'd like to know if it can be done with a formula. I've got 2 columns I need to count how many times they match between dates listed in a 3rd column. Example: On Sheet one there...
  14. C

    Using SUMPRODUCT with different array lengths

    I'm trying to copy a SUMPRODUCT formula to other cells, but I have different array lengths. Is this possible or will have to continue to copy and paste the formula to the new cell and manually adjust the arrays? Is there a way Excel can find the breaks/array length difference? Thanks, RJ
  15. B

    sumproduct multiple criteria ignore error

    Hello, I have the following two columns in A1:B4 (customer # followed by percentage) 1 0.5 2 0.9 3 0.8 4 #DIV/0! In column D i have a list of the customer #s. In column E i try to identify if the customer in column D have a percentage >=.8. I am using the below formula, but...
  16. M

    SumProduct Help!!

    Hello, New here, but pretty certain i searched through all forums for an answer to my questions before posting this. I've been stuck on this for a couple of days now. I have three columns in a table I want to use for this formula. Build Status (Column A), Data Submit (Column B) and Ship Date...
  17. F

    Sumproduct

    Hi, In sheet I have two coulmn columns: A = applications B = sector I set "define name" for the column: A = "prod_web" (range: $A$2:$A$1000) B = "sec" (range: $B$2:$B$10000) When I want to sum how many specific applications have specific sector I did: =SUMPRODUCT((PROD_WEB="ABC")*(SEC=A2))...
  18. M

    =SUMPRODUCT ..... Help Please

    Hi there SUMPRODUCT is giving me a headache as I can't figure out where I am going wrong. I have a spreadsheet with data in Columns A to W, Column E I have Named as TSStatus and Column U has been Named TSCom. TSStatus has a range E2:E20000 and holds information "Not Started", "Pending"...
  19. S

    Array Formulas Not Updating

    I'm working in a sales summary file, and I'm using the following formula: {=(SUMPRODUCT(TRANSPOSE('2013 Market Matrix'!$U$17:$BJ$17),'Top 40C by Month vs PY'!E$8:E$49))} The purpose of this is to take sales dollars from customers and run that against a series of percentages to determine sales...
  20. J

    Using sumproduct with a list of 11, non-numeric search arguments

    I need to sum the market values of a group of 11 stocks, identified by three letter tickers. I have three columns with data. Column A has 114 three-letter tickers, Column B has 114 market values associated with those tickers, Column C has the list of 11 tickers that I want a sum of market...

Some videos you may like

This Week's Hot Topics

Top