# Excluding rows in excel SUM based on different cell values

I have a SUM (A:A) cell value that calculates the sum of all cells in a certain column. However, I want to create another cell that has the sum of all cells in the same column, but exclude those cells that do not have the letter R in the neighboring cell. The reason is to calculate the sum of reconciled values only in an account.

It's unclear on whether the R is the entire cell value or part of the cell value. Here are some sample formulas for each.

=SUMIF(B:B, "R", A:A)
=SUMIF(B:B, "*R*", A:A)

The second one uses a wildcard to see if the cell(s) contain R. The first discards cells where the entire cell value is not R.

More on this at SUMIF function.

• I think he wants to exclude "R", making the formula: =SUMIF(B:B, "<>R", A:A). sorry didn't see this was 2+ years old... – gns100 Jan 31 at 19:41

Supposing that B is the column with labels...

=SUM(IF(ISERROR(FIND("R",B:B)),0,A:A)) (array formula: Ctrl+Shift+Enter)

FIND looks for the letter in column B, and returns an error if R was not found in that string. If there was an error then R was found so the if statement brings back A. If there was no error then R was not found so the if statement brings back 0. Then sum them all up.

• On re-reading, I believe you are correct. Fixed – Adam Mar 16 '15 at 16:35