2

I am trying to use the SUMIFS() formula in excel to exclude certain rows from a table, but the criteria range includes numbers stored as text.

In the picture below I want to exclude the rows where entity id is "101000". The SUMIFS() formulas I have tried all provide the incorrect solution.

I found similar problems (here and here). This is where I came up with the SUMPRODUCT alternative.

enter image description here

I am trying to see if there is an alternative using SUMIFS. The syntax of SUMPRODUCT is confusing. But more importantly it doesn't work if I have entity id's that both translate to the same number value ('0100' and '00100').

enter image description here

Chris Kiniry
  • 499
  • 3
  • 13
  • If anybody has this problem, the solutions posted worked. The approach I used was to add a character text to the front of the number text in a helper column so excel knew it was text. ="e" & B4 – Chris Kiniry May 27 '22 at 14:08

2 Answers2

3

If you are using Office 365 you can combined the FILTER and SUM functions.

First FILTER the amounts

=FILTER(C4:C9,B4:B9<>"01000")

Then SUM the filtered amounts

=SUM(FILTER(C4:C9,B4:B9<>"01000"))

enter image description here

Robert Mearns
  • 11,796
  • 3
  • 38
  • 42
  • Thanks! Can FILTER() accommodate more than one criteria? I'm using SUMIFS() because I have multiple criteria to filter on, this entity id is the only criteria that isn't working. – Chris Kiniry May 19 '22 at 16:12
  • Yes. The * is treated as an AND and the + as an OR in the include section of the FILTER function. Try this to only return the amount for 174, based on the data in the screenshot. =SUM(FILTER($C$4:$C$9,($B$4:$B$9<>"0100")*($B$4:$B$9<>"01000")*($B$4:$B$9<>"101000"))) – Robert Mearns May 19 '22 at 16:37
  • You saved me from repeating the same question of mine. – Fighter Jet Aug 15 '23 at 09:23
1

You can sum the rows whose IDs do match, and then subtract it from the total sum:

=SUM($C$4:$C$6)-SUMIF($B$4:$B$6,"101000",$C$4:$C$6)
jsheeran
  • 2,912
  • 2
  • 17
  • 32