need to solve 2 sales tax problem

[mrK1]

Hello,

I've been unsuccessfully trying to sort this out:

Some items have sales tax of 6.5% and some 7%
Need to figure out how to subtotal the combination of tax on invoice.

Example:

Please Login or Register  to view this content.

=SUMIF(C3:C232,"X",Sheet2!I11:I47)*0.065+SUMIF(C3:C232,"Y",Sheet2!I11:I47)*0.07

The tax subtotal result should be $0.40, but i get $0.82

Thank you.

[Glenn Kennedy]

Based on your example:

=SUMPRODUCT((C1:C3={"X","Y"})*(B1:B3*{0.065,0.07}))

[JohnTopley]

Using your formula I got the answer $0.40

=SUMIF(C1:C3,"X",B1:B3)*0.065+SUMIF(C1:C3,"Y",B1:B3)*0.07

(and the correct result with your provided formula =SUMIF(C3:C232,"X",Sheet2!I11:I47)*0.065+SUMIF(C3:C232,"Y",Sheet2!I11:I47)*0.07
)

Any extraneous data in your range?

[mrK1]

thank you for the responses.

i double checked, and it remains the same.

i'm attaching the file.

Workbook2.xls

[samba_ravi]

=SUMPRODUCT(($I$11:$I$13)*(LOOKUP(MATCH($B$11:$B$13,Sheet2!$A:$A,0),ROW(Sheet2!$C:$C),Sheet2!$C:$C)={"X","Y"})*({0.065,0.07}))

Please Login or Register  to view this content.

Try this formula

[JohnTopley]

The problem results from the fact that the criteria range and the sum range are different.

If you look at "Help" for the function you will see an explanation of how the ranges are determined if the are not the same size and shape.

[mrK1]

nflsales, thank you. That formula worked. However when i edit for the needed I11:I47, and if i have -blank- values on that range, i get #Value. How do i eliminate this?

Thanks for the help.