Newbie trying to compare numerical cells

[sblg43]

I am a self taught computer guy with limited excel experience. What I am trying to do is set up a "sales journal" showing our daily sales activity, weekly sales activity and monthly sales activity. I set it up but I need to know how to compare some cells for accuracy.

My "journal" has a break-down of pre-tax sales (in one cell) then sales tax (in one cell) with the SUM of the two in a third cell. No problems there. However, I then have the sales broken down into Cash, Elan, Discover and Amex. What I need to do is make sure that the combined totals of Cash, Elan, etc... is equal to the SUM of the pre-tax plus sales tax cell.

I hope this makes sense. Can anybody show me a formula or function to compare these totals and let me know if they don't match?

Thanks
Scott

 

[PA HS Teacher]

I would start with the Sum IF function.


e.g.
=SumIF(A1:A100,"Cash",B1:B100)

Would sum all values in the B column, for which the A column was "Cash"

A good check sound like it would be something like,

=SumIF(A1:A100,"Cash",B1:B100) + =SumIF(A1:A100,"Amex",B1:B100) + etc.

 

[Aladin Akyurek]

1]

=(TotalX-TotalY)=0

TRUE means OK, FALSE Investigate.

2]

=(TotalX=TotalY)*TotalX

0 means Investigate, othwerwise OK.

 

[sblg43]

Thank you for your ideas.

What I found that worked for me was:

=IF(G19=SUM(H19:K19),"YES","NO")

I love finding solutions to things like this. I almost wish I had a job that required more Excel work.

Thanks again.

 

[Aladin Akyurek]

Thank you for your ideas.

What I found that worked for me was:

=IF(G19=SUM(H19:K19),"YES","NO")

I love finding solutions to things like this. I almost wish I had a job that required more Excel work.

Thanks again.

Why didn't you also check...

=G19-SUM(H19:K19)=0

=(G19=SUM(H19:K19))*G19

while, with the latter, you can custom format the formula cell as:

[=0]"NO";[<>0]"YES"

In fact, you should also consider wrapping each term inside a ROUND to counteract the vagaries of the floating point arithmetics.