Search code examples
excelsumifs

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

Solution

  • 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