Newbie trying to compare numerical cells

sblg43

New Member
Joined
Jan 25, 2005
Messages
2
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
1]

=(TotalX-TotalY)=0

TRUE means OK, FALSE Investigate.

2]

=(TotalX=TotalY)*TotalX

0 means Investigate, othwerwise OK.
 

sblg43

New Member
Joined
Jan 25, 2005
Messages
2
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
sblg43 said:
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.
 

Forum statistics

Threads
1,147,623
Messages
5,742,208
Members
423,712
Latest member
edzubur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top