CODEWITHSUNDEEP
excelsumifs
Chris Kiniry
Chris Kiniry

Reputation: 529

Excel Sumifs using numbers stored as text in criteria

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

Upvotes: 2

Views: 1032

Answers (2)

Robert Mearns
Robert Mearns

Reputation: 11996

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

Upvotes: 3

jsheeran
jsheeran

Reputation: 3037

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)

Upvotes: 1

Related Questions