Sum the cells in a range that meet multiple criteria in excel
I have 57 sheets many of which are linked together by formulas, I need to get numbers from one sheet (A) into another sheet (B).
I need excel to search through the dates in sheet A to find all the data for the date that is selected on sheet B.
then I need it to search in sheet A threw the data for that specific date and select and sum all the data that is catagorised as ROM and put the total in the Cell in Sheet B called ROM Tonnage.
Answer:
To find all data for a specific date you can use excel tables or a pivot table.
Sheet A
Sheet B
Excel 2007 Formula in cell C3:
Excel 2003 Formula in cell C3:
Explaining excel 2007 formula
SUMIFS(sum_range, criteria_range1, criteria1,..) adds the cells specified by a given set of conditions or criteria
Explaining excel 2003 formula
Step 1 - Cells to sum
Step 2 - Find values equal to date
becomes
--({40544; 40546; 40544; 40545; 40547; 40546; 40546; 40545; 40546; 40545; 40545; 40545}=40544)
becomes
--({TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})
and returns {1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Step 3 - Find values equal to criterion
becomes
--({0;0;0;0;"ROM";0;0;"ROM";0;0;"ROM";"ROM"}="ROM")
becomes
--({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE})
and returns {0; 0; 0; 0; 1; 0; 0; 1; 0; 0; 1; 1}
Step 4 - Putting it all together
SUMPRODUCT(A!$C$3:$C$14, --(A!$B$3:$B$14=B!B3), --(A!$D$3:$D$14="ROM")))
becomes
SUMPRODUCT({85;83; 17;73; 48;1;41; 83;46;79;33;55},{1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0}, {0; 0; 0; 0; 1; 0; 0; 1; 0; 0; 1; 1})
becomes
SUMPRODUCT({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
and returns 0 in cell C3.
Download excel file for this tutorial.
sum data.xlsx
(Excel 97-2003 Workbook *.xls)
Functions in this article:
SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays
SUMIFS(sum_range, criteria_range1, criteria1,..)
Adds the cells specified by a given set of conditions or criteria
Recommended blog posts:
Interested in sumifs and sumproduct functions? You have to read these blog posts:
- Sum values between two dates with criteria in excel
- Count matching values in one or more columns in excel
- Quickly highlight records in a list using multiple criteria in excel
- Quickly highlight records in a list in excel (AND logic)
- Sum unique distinct invoices in excel 2007
Related posts:
Count unique distinct values that meet multiple criteria in excel
Excel 2007: Color cells that meet criteria using conditional formatting
Count matching cells in date range in excel
Extract all rows from a range that meet criteria in one column in excel
Quickly highlight records in a list using multiple criteria in excel
Recommended categories
I'm in a similar situation as the example above, but I have three sheets and three conditions. Sheet1 has three columns: Site, Quarter, Boxes; Sheet2 has two columns: Site, Designation; and Sheet 3 has three columns: Designation, Quarter, Boxes. I want to sum the last column (boxes) in Sheet1 to the last column(boxes) in Sheet3 based on criteria in all three sheets.
The common links between the sheets are Sheet1-Sheet2: Site, Sheet2-Sheet3: Designation, Sheet1-Sheet3: Quarter. I having difficulty getting it to sum only facilities with a certain Designation for a given Quarter. I am trying to use SUMIFS but am not sure if this is the appropriate formula. Any thoughts on how I can make this work?
klm,
I am not sure I understand. Check out the file I created:
klm.xlsx