sumproduct

Hi there, I have two tables for labor rates by function and by year. One table for GBP currency, and one for USD currency. I then have a table that contains labor hour requirement by contract (many rows). Each contract is either native to GBP or USD. I'm trying to use the following formula to...

Hi I am trying to find Table1[ID] that matches with Table7[Name], "Singer" The only way to find this is if Table1[ID] = Table7[IDs]) and Table7[IDs] = Table7[Name], "Singer" but it keep showing error when I try to count it, how do I go around this?

Not even sure where to start. The formula shown is supposed to read the various grades of material in P,Q,R,S column and then find which grade bin it sits in from the reference table and return the BIN type from Row 5. This used to work in an old version of the sheet I have but I'm trying to...

I have a database that shows a bunch of courses required by my employees and then a column for every week in the year to track at which point the employees attend the course and get credit for it. At any given week I want to know the percentage of students that graduated from class as an...

I'm trying to build a formula in my rates tab (see below) to show the minimum price of the unit found. I've started trying to build the SUMPRODUCT statement but getting a value error (see rates tab below formula bar). I noticed the dates on the comps tab were string rather than date so I added...

Hi all, I work for a finance company, I receive a large data set via a webhook from a cloud software provider. We use the dataset to do client reporting. The dataset has all of the investments each client has. There is 217 rows, across 15 different clients. When we present the information to...

Hello I'm hoping you can help if possible please. I have a spreadsheet with 3 tabs, on tab 1 is the formulas / calculations, tab 2 holds all the data and tab 3 has dates (start/end date). I am trying to calculate how many occasions of absence an employee has within a 6 month period and...

Hi All, I currently have an Excel for my Sunday league team which includes the availability, goals, assists, clean sheets and MOTMs of each player for every game of the season. I am currently struggling to find the right command string to provide the total goals, assists, etc specifically for...

hai, can everyone help me to change this formula to vba code : =SUMPRODUCT(($E$2:$E$63184=E2)*(U2>$U$2:$U$63184); ($F$2:$F$63184=F2)*([U2>$U$2:$U$63184))+1

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