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.

Thanks in advance, Sammy


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 '19 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.