sumproduct

I have got an issue that I can't workout. I need to perform a SUMPRODUCT with multiple AND and OR criterias. something like the following...

Is it possible to use SUBTOTAL in conjunction with SUMPRODUCT? I'm using the following formula to calculate a weighted average price increase. =SUMPRODUCT(N5:N3400,AQ5:AQ3400)/SUM(AQ5:AQ3400) column N= price increase percentage column AQ= sales I would like to add SUBTOTAL to the formula in...

Hi! I am having a lot of trouble calculating a weighted average and ignoring blank cells. I understand what to do if the data is in an array, but I am working on a scorecard that will be printed out. The odd formatting is making it very difficult for me to use the sumproduct function and...

I have been using this formula {which is working fine} SUMPRODUCT( N( MOD( COLUMN( $I37:AJ37)) -6, 7)= 0), $I37:AJ37) +F37 where the first part creates a mask for every 7 column, Middle part is numbers, text and blanks which only every 7th is needed, and the last part is a offset value. I...

Hi all, I currently have the following setup: I have some numbers in column C and D, and I use a simple sum() formula in column B. However, I also want to be able to click on the cells in column B and see the value in the formula bar. But I want to keep the formulas in the meantime. What I...

I have a database with auto generated data. On a separate sheet in the same workbook I can pull the information I need with SUMIFS but in a certain situation I need to multiply two cells first then sum the answers together without adding another column and divide by 12 to get Feet.

Hi Team, This is my first post and apologies if I am overstepping any rules here. I have gone through similar posts, but didnt find a way forward. There are similar threads, but the issue here is a bit different. The problem Statement is as follows - I need to calculate the time taken before a...

Hey All, I have the below table, and instead of having "B", I have formula that goes : If(xxx,"Text", ""), and as it stands B should then reflect: "". When I use the formula: sumifs(range Y,Range Y, "<>"), the formula reverts 3; but if I use sumproduct ((range X <> "")*(Range Y)) the formula...

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