sumif

Hi all, Hoping someone can help me with this. I'm guessing it super simple, but I can't find a solution anywhere online. Basically, what I'm trying to do is count the number of times each cell in a range (G4:G9) is not blank and is less than 70% of the value of the cell that is 2 columns...

Hello, I have two sheets, named: 1. Master Data - This sheet will be updated daily by pasting a system report over the existing data 2. 2022 - I want to calculate some stats from the master data sheet on this sheet The gist of this is, I need to report on people who leave between 0 and 5...

Hi, I am trying to sum up the amounts belonging to certain categories through their ID number but cannot think of any way to find all matching items - xlookup only finds the first item and I can't have a spilling function like filter. The ID number/amount table and ID No./category table must...

Hello, hope you can help. I would like to use a formula to give the count of complete projects in [time range] where there is a total of <=1 hour spent on 'review' over the whole project - example sheet below. In this sample the desired answer is 2 as only project A and B meet the criteria...

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

Hi have the below formula i am trying to edit the outcome, =SUMIFS('Annual NEW'!$T:$T,'Annual NEW'!$B:$B,L$5,'Annual NEW'!$E:$E,$E143,'Annual NEW'!$H:$H,$F143) i am trying to find a way that if those criteria's are met then the sum should be Column M + N + O + P and a similar case where...

Hi everyone, I have a question with two columns: if a column of mine holds a value such as "100 012", "800 002", "500 012", or "800 004", and my second column or updated value holds a value such as "800 007", "800 008", "800 003" or "97", I want the balance of that specific row. Realistically I...

Hey Everyone, I'm getting a little confused on writing this formula. This is what I have: =SUMIF(F9:F25,"*BMO*",K9:K25)+SUMIF(G9:G25,"*BMO*",K9:K25) This is what I need to add: My range is currently set to K9:K25, however I want the sumif range to use the value in J9:J25 instead when there is...

I have a table with 3 columns: transaction date, costs/revenues, tax/deductable tax. Each row is one revenue or cost and the dates goes on multi-year seamlessly (i.e., I have a table that has this from 2019). What I'd like is to give a date in a cell (say A1), and have a formula that outputs a...

Hello Sheet 1 Sheet 2 In sheet 1, I need to know how many times each SMT has someone absent with 1 occasion, 2 occasions, 3 occasions and so on summed from sheet 2 So, using the above examples, Bob would show In the above example sheet 2, I've collapsed some columns to protect private...

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

Hi, I would like to sum data in a column based on the results of 2 other columns. Machine 1 Monday 3 Machine 3 Wednesday 2 Machine 2 Monday 5 Machine 1 Thursday 7 Machine 1 Monday 6 So i need to sum column C in a table Machine 1 Count Machine 2 Count Monday Monday...

Using last years actuals for January and February and using this years January results +February forecast. If the customer's year-over-year variance is positive they are label as a gainer, if their year-over-year variance is negative then they are labeled a decliner. This is the formula I was...

Based on this table, I need help on creating a formula in K3 to count and in K4 to sum based on the following condition In K3 --> countif B1:G1 = K3, A3:A5 = K1, B3:G5 is number...

Hi, I have a list of codes (column A), and amounts of money (Column B). I would like to sum all of the codes but where the codes are the same, only have the sum for the first instance of that code from the list. eg see the example below TIA CODE AMOUNT RESULT CAT 5 11 DOG 6 16...

Hello I am trying to track my energy usage and have a spreadsheet with two sheets ABCDEFG1No.Period FromPeriod toElectricGasVATTotal2101-Mar-2229-Mar-22 ABCDEFGHIJ1No.Period fromPeriod toFuel / StandingTypePrevious ReadingLatest ReadingUnits UsedKWH Rate or Standing Charge Daily Rate...

Question: I have a report with several columns & rows and I want to sum the entire range if the column header (first cell in column) contains certain text - meaning, Column A1 may contain "9:00AM Admission" or "10:00AM Admission", etc. If the column header contains the word "ADMISSION" I want...

I am using the SUMIF formula to pull the sum of each location by month from corresponding sheets, See the photo below for my comparison sheet I am creating. We will use the first location "Ann Arbor Education Center" as an example. You will see the current formula I am using above, this is the...

Have tried searching the forums but can't seem to word my question correctly. Here is a basic table:- Date Type Product Qty 01/12/2022 Order Blue Shirt 1 02/12/2022 Order Blue Shirt 1 03/12/2022 Order Blue Shirt 2 04/12/2022 Order Yellow Shirt 1 05/12/2022 Order Blue Shirt 1...

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