sumproduct arrays

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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