sumproduct

Hi, I have a collection of cashflows which I am looking to consolidate onto a master sheet (all within the same workbook). Each individual tab has the same account code and date references. I have a name range for the individual tabs but I cannot seem to find the correct syntax to pull the...

I have dates in column A, (but some blanks), then column B has X, Y, or Z. The zero's were messing up my Countif and including them in the January total, but in the end I managed to count the number of occurrences for each moth by using : =SUMPRODUCT(--(MONTH(A2:A500)=1),--(MONTH(A2:A500<>0))...

Hello, guys, I want some help with my excel functions (Sorry I don't speak English so my post will likely be a challenge to read) Here's the sheet (sheet 2) : the function(on the Left): 3/10/2023 3/11/2023 3/12/2023 3/13/2023 3/14/2023 3/15/2023 ABC...

Hi, i have this table (could not upload mini sheet) on multible sheets week is merged cells but here i have just wrote 1/2 in all cells Week: 1 1 1 1 1 2 2 2 2 2 user aa bb cc dd ee aa bb cc dd ee task 1 1 4 2 3 2 2 8 4 6 9 task 2 3 5 9 3 2 7 4 2 8 3 sum...

I have two worksheets. One has the source table: And another has an aggregation table: To get the numbers from the first table to the second I use the following formula: =SUMPRODUCT(INDEX(FS!$A$2:$Z$250,0,MATCH(C$1,FS!$A$1:$Z$1,0)),--(FS!$B$2:$B$250=$A2))/1000 It basically checks the...

Hi, Just wondering... With modern Excel's improved abilities to handle arrays and to spill, and considering the awesome FILTER function - I'm just wondering if there is still a need that only SUMPRODUCT can fill in today's Excel world. And if the answer is, "Yes, there are things that only...

I'm trying to come up with a function that ranks scores sequentially with ties receiving the same rank. I've gotten really close by using: =SUMPRODUCT((I5>=I$5:I$129)/COUNTIF(I$5:I$129,I$5:I$129)) In fact, this works perfectly when sorting smallest to largest: In a ranking of 125...

I am trying (without success) to use either Countifs or Sumproduct to count how many cancellations were made with less than 48 hours notice. So, here Status = Cancelled and Start Date >= 01/11/2022 and Start Date <= 30/11/22 AND where the difference in start date and start time and cancellation...

Hello Excelers, I think a sumproduct is the solution but honestly I never understand how they work :( If sumproduct does work, would you mind explaining how the formula is built/how it is working? I want to add up stage 1 - 4 if it is between a certain date, say 01/09/2022 and 30/09/2022. I...

I am using the table tblSummary below to sum data from other tables in the workbook. All table names are in a named range called nrSheetParameters. Some of the tables have additional columns between each month so [sum range] criteria needs to be the same column name in each table as the column...

I have a lambda function which will not let itself be included in a SUMPRODUCT depending on the YEARS comparison. All other parts of the formula are working but stick the LAMBDA function result in there and it just won't have it... Why ??? Row 5 in blue are figures as a result of the lambda...

Hello Everyone, I have an Excel question. I have two sheets- tab a is the source data, tab b is data from a BI report. I need to match the IDs on both sheets, both in column A, and THEN sum all the matching values in the balances column to compare the report bal to the source bal. 1) the IDs on...

Hi everyone, Every month I get this table (Clients) where I am given costs broken out by rows of clients and columns of weeks. However, I need to summarize these values by Client and Month instead of week (Sheet2). Pivot tables don't seem to read across columns and my current sumproduct...

I am trying to perform a sumproduct with table references and when im trying to add a extra condition it will no longer work. Below are the two examples there must be something simple I am missing the @Type field is just matching text, if anyone could please guide me with the correct syntax to...

The lower table refers to the upper table. The upper table is somewhat successfully dynamic (side note: if I could use: =SUMIFS($G$4#,$M$4#,$O4#,$K$4#,Q$3#) instead of: =SUMIFS($G$4:$G$1000,$M$4:$M$1000,$O4#,$K$4:$K$1000,Q$3#) that would be cool) Is there any way to modify my formulas in the...

A certain company has taken a loan in installments. Depending on the number of years that have passed since the installment was taken, the company has to pay a certain multiple on it. The loan installment that was taken first also gets paid out first. It is possible to make partial repayments...

Hi! I'm trying to have excel return a date overlap. The criteria I have for this are, If the name (Column A) is the same, RA (Column B) is different, the end date (Column D) is not the same, and there's overlap then flag this row down. So for example, Row 13 and row 7 should be flagged down...

Version: Excel Online I want an average of the time (in days) between two milestones for many different people (one person per row), but I want the formula to ignore that row if there isn't a date in BOTH date cells, since it throws off the average in a major way. Example "TABLE_1" EE Name...

Hello people! I am quite new to proper work in excel and I need some help! I have this table with sales and revenues from the months december 2014 and january 2015, the sales and revenues are made in 5 currencys, FX rates are given in another sheet . I need to calculate everything in euros...

I've got data organised in the following way: 2 first columns are identifiers and the rest of the columns are values by year (a year per column). So I need to summarise it in a different table based on 2 identifiers an by year. So each value in the summary table should match the combination of...