Search code examples
excelsumproduct

Ignore non-numeric values in sumproduct formula in excel

I tried to use sumproduct formula, but it doesn't ignore non numeric (text) values.

Tried double unary. But nothing works

=SUMPRODUCT((A2:A8="TX")*B2:B8)
=SUMPRODUCT(--(A2:A8="TX")*B2:B8)
=SUMPRODUCT((A2:A8="TX")*--B2:B8)
=SUMPRODUCT(--(A2:A8="TX")*--B2:B8)

State   Sales
UT  75
CO  100
TX  125
CO  125
TX  150
TX  a
CO  50

I know sumif can do, but im trying to achieve the same from Sumproduct, coz i need to apply it in larger frame

Solution

  • Try this one

    =SUMPRODUCT((A2:A8="TX")*(ISNUMBER(B2:B8)))

    To get the logical values. If you want the result:

    =SUMPRODUCT((A2:A8="TX")*(ISNUMBER(B2:B8)),B2:B8)