## Sum cells based on criteria

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:**

I highly recommend a pivot table for this task if you have lots of data to work with. It is incredibly fast and easy to work with, however, this article demonstrates a formula.

Excel 2007 Formula in cell C3, sheet B:

The SUMIFS function lets you add numbers based on multiple conditions and returns a total, it was introduced in Excel 2007. If you own an earlier version than 2007 then see the formula below.

Excel 2003 Formula in cell C3, sheet B:

The SUMPRODUCT function is incredibly useful and easy to use, all conditions are shown in formula above.

### Explaining Excel 2007 formula and later versions

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.

Sum values between two dates and based on a condition

In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]

Running totals based on criteria

Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]

The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all […]

Find empty cells and sum cells above

Is it possible to quickly select all empty cells and then sum cells above to next empty cell? Can I have […]

Sum numerical ranges between two numbers

This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]

Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]

To extract groups from cell range B3:B10 I use the following regular formula in cell B13. Weekly Blog EMAIL Email Welcome! […]

The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]

The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous […]

### 2 Responses to “Sum cells based on criteria”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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