gingerbreadgrl
New Member
- Joined
- Aug 19, 2019
- Messages
- 48
Hi,
I am working on an accounting workbook. My current formula is manually done, but I would like, if possible, for the formula to look at all the rows, take all rows with a January date, and automatically sum them by category. I also don't know how to subtract the credit/debit columns. Here is the current formula (located in Worksheet 2 in the month column):
=SUMIF(AccountActivity!$F$2:$F$71,Summary!$A$2,AccountActivity!$D$2:$D$71)
This is the formula for January, I manually entered the numbers for rows 2-17 because that is when the January items end. But, I'm wondering if I can have the formula automatically go through the date column instead so I don't have to change the formula for each column in the Summary worksheet.
I also would like to subtract the credit column from the debit column, I tried this but it gave me an error.
=SUMIF(AccountActivity!$F$2:$F$71,Summary!$A$2,(AccountActivity!$D$2:$D$71-AccountActivity!$C$2:$C$71))
Here is an example of what the 2 worksheets look like:
Worksheet 1: Account activity
Note: Expense Type column is a data validation dropdown with options from a list.
Worksheet 2: Summary
Note: Expense Type column is a data validation dropdown with options from a list.
Any tips on the formula would be much appreciated! Hope everyone is staying healthy!!
Best,
Gingerbreadgrl
I am working on an accounting workbook. My current formula is manually done, but I would like, if possible, for the formula to look at all the rows, take all rows with a January date, and automatically sum them by category. I also don't know how to subtract the credit/debit columns. Here is the current formula (located in Worksheet 2 in the month column):
=SUMIF(AccountActivity!$F$2:$F$71,Summary!$A$2,AccountActivity!$D$2:$D$71)
This is the formula for January, I manually entered the numbers for rows 2-17 because that is when the January items end. But, I'm wondering if I can have the formula automatically go through the date column instead so I don't have to change the formula for each column in the Summary worksheet.
I also would like to subtract the credit column from the debit column, I tried this but it gave me an error.
=SUMIF(AccountActivity!$F$2:$F$71,Summary!$A$2,(AccountActivity!$D$2:$D$71-AccountActivity!$C$2:$C$71))
Here is an example of what the 2 worksheets look like:
Worksheet 1: Account activity
Note: Expense Type column is a data validation dropdown with options from a list.
Posting Date | Item | Credit | Debit | Balance | Expense Type |
1/2/2020 | Office Evolution | 200 | 1500 | Rent | |
1/4/2020 | Comcast | 150 | 1350 | Utilities |
Worksheet 2: Summary
Note: Expense Type column is a data validation dropdown with options from a list.
Expense Type | January | February | March | April | June | July | August | September | 2020 Total |
Rent | 200 | ||||||||
Utilities | 150 |
Any tips on the formula would be much appreciated! Hope everyone is staying healthy!!
Best,
Gingerbreadgrl