Excel Sumifs using numbers stored as text in criteria

user_6178088 0
Viewed 86

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

sumifs excel
2 Answers

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

user_50500

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)
user_47425700
Related