sumproduct

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

Hello, I have the following code that I can't figure out how to adjust - Sub SumData() Dim HSArr(2 To 250) As Variant Dim HSVal As Long For HSVal = LBound(HSArr) To UBound(HSArr) HSArr(HSVal) = Cells(HSVal, 1) & "_" & Cells(HSVal, 2) Next HSVal Const dFormula As String _ =...

Table 1 result is fine, but the Table 2 is not working. Please help. ABCDEFGHI1Table 12productC40123productA16024productB00025productA0026productB01027productA2002Result=16891011Table...

Hello (again) mrexcel forums, I'm working on a formula which outputs an array of values in an n*m grid, where the size of the grid may vary a lot between use cases. I've been getting good mileage with using arrays as named variables in equations, but i'm having trouble with the MIN/MAX...

Good morning! I am struggling with a formula and have been so for days so am finally admitting defeat and reaching out. I could use some help figuring out how to combine sumproduct with more than one countif in a situation where I need to count the number of times any value within a list is...

Hi, I have two-pronged question though the most important one will be the 1st bullet and the less important will be the 2nd bullet. The uploaded excel will show the same format for the aggregate data on the top and the different sets of data outlined in black below it. Unsure if sumifs and...

I have a range name that consists of non-contiguous columns. I can use the (space) Intersection operator with functions like SUM and AVERAGE , but get #VALUE when I try it with SUMPRODUCT. Example: if range myrange consists of A:A,C:C =SUM(myrange 3:3)=26 But =SUMPRODUCT(myrange...

ABCDEFG1ProductA22222ProductA213ProductB2204ProductA215ProductC2126789Expected result isB1*C1*D1*E110B2*C2*D2*E111B3*C3*D3*E112B4*C4*D4*E113B5*C5*D5*E114SUM(B9:B13)152816Criteria: if BLANK in any row, ignore the row in calculation17I have done SUMPRODUCT for B,C,D and how to multiply E1 to each...

I've been trying to use OFFSET formula nested within the ISNUMBER(MATCH) combination to get a dynamic range but can't get it to work. There's probably an easier and more elegant way to achieve what I'm trying to do to be honest but I thought about trying to see if this will work. First version...

How to SUM values in a table, with criteria based on three columns, in each one column the criteria are many values on vertical ranges? SUMIFS works, but for only one range as criteria, the other criteria range must be a single value.

Hello everyone, I would like to count with your help, Im trying to do a tenure counting of persons per each month of the year, if the person is still active i do not insert date in the leave date column. I would like to use sumproduct if possible, and ignore the black leave date cells from the...

Hi, I’m trying to average the total minutes of the first N -- in this case, 3 in lieu of N -- movies watched by a client. Please see my sheet: docs.google.com/spreadsheets/d/1oTwnB5u7y4KqAwamvjsEats3JiTPpYhEkOqUvOzEkDE In column A, you can see the extracted numbers from the minutes texts so...

Hello! Perhaps you could help ... Column A value Column B item start date Column C item end date Need to sum only the values that have item duration divisible by 12. I believe it is a combination of SUMPRODUCT, MOD, DATEDIF and perhaps "--", but not sure how to put them together. I know...