Excel Sumifs using numbers stored as text in criteria-CodePudding

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 (

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').

CodePudding user response：

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)

CodePudding user response：

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"))