Sep 25 2023 07:18 PM
It's been a while since I've used Excel. A number of new things since then, and I'm having trouble with a formula. Any assistance would be greatly appreciated as I'm not finding an answer in the Help.
I'm trying to put together a very rudimentary "accounting" spreadsheet for a small business. I want to keep a running tally of certain items such as Bank Merchant Fees when I sell something. In my basic "checkbook" I have dropdowns for various vendors and accounts. So, if I add a merchant fee, as my example, I want the value subtracted from my running balance but also added to a running tally on a separate worksheet. In a cell to the right of the running balance, I added the following:
= IF(D3,(IF(C3="Bank Merchant Fee",General!B7=General!B7+D3,"")),"")
where D3 is the amount of the expense, C3 would have the account (in this case, Bank Merchant Fee, from a pulldown), and B7 on the General worksheet is the running tally of fees. If there's an expense in D3 but it's not a Bank Merchant Fee, then nothing should happen. However, when I test this, nothing happens at all. Period. I'm not seeing a value appear on the General sheet.
What am I missing? Thanks.
Sep 26 2023 01:08 AM
A formula on one sheet cannot directly set the value of another cell.
General!B7=General!B7+D3 does not assign a new value to General!B7. It is a comparison that returns TRUE or FALSE - in this case FALSE since D3 is not zero.
Do the following instead:
I assume that A7 on the General sheet contains Bank Merchant Fee. If not, enter it there.
In B7 on the General sheet, enter the formula
=SUMIFS('Check Book'!$D:$D, 'Check Book'!$C:$C, A7)
Replace Check Book with the real name of the sheet where you enter the categories and amounts.
If you have other categories in column A of the General sheet, you can copy/paste the above formula, or fill it down.
Sep 26 2023 07:41 AM