sumproduct

Hi there I have a specific excel question and was hoping someone out there might please be able to help. I have this formula which is functioning: =SUMPRODUCT((Income!$A$4:$A23>=B10)*(Income!$A$4:$A23<=C10)*(Income!$F$4:$F23)) However it doesn't work if there are any cells that aren't numbers...

I've attached a link to a data table. Here's what I'm trying to do. For each cell in column G, I need to calculate the revenue-weighted average price of the product lines EXCLUDING the product line associated with the row of the cell that I am calculating in column G. Cell G2 is a manual...

Hi there, I'm trying to add a formula into each blank row of my data set in column C. (see cell C5 on image, as an example of where the formula has populated, and cell C11 where it hasn't) The formula in column C is a weighted sum product formula i.e. =($B3*C3)+($B4*C4)+($B5*C5), so this...

Hi. I am trying to use the sumproduct formula, but I need to apply it to about 300 cells and each cell has different criteria. I don't want to have to go cell by cell to put each criterion in and am wondering if there is a faster/better way to do this? To explain this further in column A I...

Hi all, first time post to the forum and I was wondering if any excel genius could help. I need to match match match multiple and sum against multiple columns of information, I'm nearly there but cannot figure out how to finish the formula. Here is where I am so far...

Hello, I currently have these formulas that calculate data from another tab's table, but if I filter data out of the table, my formulas still calculate the filtered out data. I'm pretty new to excel, but I've read a few forums on using SUMPRODUCT to get the results I want but not sure how to do...

Hi! I've used the formula =SUMPRODUCT(SUMIFS(E19:E27;A19:A27;{"Team 1";"Team 4";"Team 8"})) to calculate the forecast for specific teams but I want to use the cell reference (A20;A22;A26) instead of the actual team name. Am I using the wrong formula for that?

Hello - I have lines of data (about 7,000) that contain a dollar value ranging from $0.00 to over $1,000. I was hoping to create a formula that calculate the $range the value is in. Example, if the cell value was $13.50, the formula would return $.01 - $25. $350, would return $300 - $400, $0...

I have been trying to solve this for about a week and cannot seem to figured this out. I have several sections of a worksheet that is for 10 types of work being performed, the status of the work, and an estimate of the cost of that work. It looks like this, but has a lot of other items in...

Hello, I have a sheet of fictional data here: duplicates. Below is a screenshot for convenience. I'd like to list, in the duplicate column, duplicates of values in the id column, with one condition: that the corresponding row in the complete column must contain a 1, not a 0. Furthermore, I'd...

Hi everyone, I have been searching for a way to calculate a range of cells that meet two date criteria, however the problem I have is figuring out how to use SUMPRODUCT or similar as the sum range is dynamic. My goal is that users will be able to enter a range of figures into an 'Hours' Column...

Hi, I am trying to COUNTIFS function with a few different conditions: =countifs('Key Property Info'!A:A,"media",'Key Property Info'!H:H,{"contract sent","apps sent"}) I would like excel to return the value of the the contract sent and apps sent figures, where they are applicable to 'media'...

I have a structured table called Prod_TechT that is in B82:IX106 that I what to count the Number of "Y"s in every seventh column in IW82..106. This worked =SUMPRODUCT(N(MOD(COLUMN($B82:IV82)-1,7)=0),N($B82:IV82="Y")), but I want to converted it to the structured names. i.e...

Hi, I need some help with a spreadsheet that I cannot change the source data (raw data), which is extracted from a report. Basically, I need to identify the products with highest satisfaction rating among a plethora of products, in a single column with data that goes from smartphones, tablets...

Dear Excel-Pro Community, I need Your help. I am looking for a solution, where I can do the following: I have a table, where I want to build a SUMPRODUCT formula, with multiple conditions. I am already OK with the conditions, but in the table I do have a column (Named: Weighting), where I have...

I have the following data table: Jan Feb Mar Apr May Jun Jul Aug Sep Apple 2 3 1 5 6 3 8 1 4 Pear 5 2 7 0 8 1 2 6 7 Banana 3 6 1 5 3 9 7 8 0 Current month is June for example, then I would like to sum up for Banana line, all numbers up till Jun. That is 3+6+1+5+3+9 = 27. Is there...

I want to Rank Top 10 Products along with figure from another sheet as below. The other sheet contains similar Products name in multiple category with the total from each category. I tried INDEX & LARGE formula, but it did not work well because Total figure would become bigger. Would appreciate...

Hi, I have this piece of code: With Workbooks("MyWorkbook.xlsx").Worksheets("MyWorksheet") Workbooks("OtherWorkbook.xlsb").Worksheets("OtherWorksheet").Cells(i, "MR").Value2 = _ .Evaluate("SUMPRODUCT((W2:W150000=MYRANGE)*Z2:Z150000,AA2:AA150000)") End With The code seems to work if I...

I have two columns, AR (Program Name) and BA (Yes or No), and I want to count the number of 'Yes' responses based on the program name. I've tried COUNT, COUNTA, COUNTIF, COUNTIFS, SUM, SUMIF, SUMIFS, SUMPRODUCT without success. I get returns of 0, all the Yes', and #VALUE. I've been trying to...

I have the following code, I am trying to use the SUMPRODUCT function, I want to loop through all the concatenated values in A and B and then use that info in the SUMPRODUCT. But my code isn't doing anything or throwing up any errors. I don't see why it doesn't work? Sub SumData() Dim val As...