sumif

I have tried a number of formulas to get this, and I keep getting circular references. My use case is a construction project budget, where I have a hierarchy of categories. In a single table, I want to make the inputs AND see the rollup (I am familiar with PivotTables, and they do not fit this...

Hi, I have a large dataset made of orders (duplicate order id in column A for orders containing multiple items). I need to calculate total price of order based on unit price/item. I have tried to sumif(take(filter but it doesn't work. Same for a countifs based on multiple criteria located in...

Hi, I'm trying to create a formula in a table column that sums values based on a date/time range as long as the time difference between any values within the range and the value in the current row is roughly less than 2 minutes. The issue I'm running into is a spill error from said calculation...

Hi, I'm struggling to figure out why this formula wont work, thought this would be an easy one for me! I have a quarterly budget against business unit projects, several projects per business unit that I want to summarise. Q1 to Q4 budgets for each project listed in columns L to O. The criteria...

Hello - In my example below, I'm trying to use an array formula to filter the Top 5 "Accounts" in column AA based on the "Sumif Labor Cost" in column AB. I want to ultimately do this with a single array formula in cell AA13. I've provided the rank in column AC to show the top 5 items that I want...

Hi everyone, I'm working on a shift planner that will include cells with numbers and text. I want to sum the numbers of these cells that have numbers and text. My shift patterns will follow a strict criteria. Where X is a number: Example 1: X Standard Example 2: X Standard X Lower Example 3: X...

Hi, I'm hoping you can help. I'm trying to automatically add some values of a Bill of Materials. I have numbers in column A which represent the level in the Bill of Materials, column B is the price, column C is where to totals should go. The criteria is that if the next row is greater than the...

Hi all, I hope you're doing well. First of all, sorry for bad explanation as my main language is not English. I'm currently using Excel 2021 and trying to experiment with Dynamic Array formula. It's quite helping with my daily Excel use. 2 of my latest question was asking about Dynamic Array...

Hello, Apologies is this is a silly question... but can the criteria in a SUMIF() be a range of values? For example I have: =SUMIF(A1:A100,7000,B1:B100) I have a list of product codes in column A and values in column B - I'm looking for product code 7000. All the 7000's are added up and so I...

Hello All, I have a formula that works as I want combining sumif and sumproduct. However, I am referencing another workbooks so I would like to use only sumproducts to make this happen. I have excel 2019 and don't have any power query functions...

I need a formula that will sum together the values of the 4101, 4119, and 4122 that are labeled March in row 9. I can get it to return the first value the index match throws back, but I am having trouble adding the sum formula into it. Help would be much appreciated.

I have extracted some data from our employee database. It's 10,000+ rows. The first row of the employee is their name, a hypen, then their 9-digit SSN (11-digits including the two hyphens in the SSN). After each employee is 2-7 rows of deductions. The last row of the employee has the total...

I have tried all that I could find on this site. What I am trying to do is Sumif the colums: Column E,Owner, uses dropdown Coumn F is the the amount paid: =IF(E20="Ian","$.50","") Column G,Payment,I want to total all of Column F: I have: =sumproduct(sumif($E2:$E49,"-ian",$F2:$F49)) also have...

Hi, I have below sheet that has multiple rows for each employee ID and what I need is to get a proper formula to sum amount 1 and sum amount 2 for each employee, can you help pls? ABCDE1Employee IDAmount 1Amount 2Sum Amount1Sum...

we want to do following first we want to use unique value from sheet1!B1:B to Sheet2!B1 after that sum if condition match in Sheet2!C2 after that sum if condition if match in Sheet2!D2 query sheet...

My question is in the image below which relates to a complex way to sum columns according to a number of criteria. I hope someone might be able to help

=SUMIFS(AD2:AD1000, AF2:AF1000, "*123*") The formula above is supposed to find all combinations of 123 in column AD and sums up the corresponding cells in column AF. By combinations of 123, I mean: 123 132 231 213 312 321 The code just returns the value "0" to me. What can be done? Take note...

How would I add SUMIF column "C" quantity to this formula =SUMIFS(A:A,A2,C:C) I want to total up how many red Apples there are. A B C D E Apple Red 2 Oranges Green 3

I have an Excel table that is called Withdrawals, column1 is the date column and column2 is the amount. I'm trying to get a sum of column2 where the year in column1 is the current year. My formula looks like this =SUMIF(YEAR(Withdraws[Date]),2023,Withdraws[Amount]) Excel is telling me that...

Hello, I am trying to figure out a formula to work out the cost of ingredients in different stores and then also look at the cost for the whole recipe at the same time. I have an example with really basic amounts in it just to give an idea. I have built it with a really basic =(A1 * I1) + (A2...