excel sum the values of column B in all rows with column A numerical value not ending with 0-CodePudding

Let's say I have a table like this (price is column A, qty is column B)

I would like to sum the values of qty in rows with price not ending with 0.

In this case, would like to know what formula can give me a number 350 (250 100) (as prices 44.0313 and 44.0399 are not ending with 0).

Thanks,

CodePudding user response：

Try

CodePudding user response：

Try MOD() Function,

• Formula used in cell A9

=SUMPRODUCT((--RIGHT(TEXT(MOD($A$2:$A$7,1),"#.0000"))<>0)*($B$2:$B$7))

Each Formula Break Down

• Formula used in cell D2 --> Extracts the decimal part,

=MOD(A2:A7,1)

• Formula used in cell E2 --> Converts To Text,

=TEXT(D2:D7,"#.0000")

• Formula used in cell F2 --> Extracts the last character,

=RIGHT(E2:E7)

• Formula used in cell G2 --> BOOLEAN LOGIC,

=--F2:F7<>0

• Formula used in cell H2 --> An array of TRUE's & FALS's multiplies with the corresponding values

=SUMPRODUCT(G2:G7*B2:B7)