Auto sum categories based on date formula

[gingerbreadgrl]

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.

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

 

[GraH]

I would define the month names as dates, like 01/01/2020 and format it as "mmmm" to show month name.

Book1
	B	C	D	E	F	G	H	I	J	K	L
3	Expense Type	januari	februari	maart	april	mei	juni	juli	augustus	september	2020 Total
4	Rent	0	200	0	0	0	0	0	0	0	200
5	Utilities	0	0	0	150	0	0	0	0	0	150
Sheet2

Cell Formulas
Range	Formula
C4:K5	C4	=SUMIFS(Sheet1!$D$2:$D$100,Sheet1!$F$2:$F$100,Sheet2!$B4,Sheet1!$A$2:$A$100,">="&Sheet2!C$3,Sheet1!$A$2:$A$100,"<="&EOMONTH(Sheet2!C$3,0))-SUMIFS(Sheet1!$C$2:$C$100,Sheet1!$F$2:$F$100,Sheet2!$B4,Sheet1!$A$2:$A$100,">="&Sheet2!C$3,Sheet1!$A$2:$A$100,"<="&EOMONTH(Sheet2!C$3,0))
L4:L5	L4	=SUM(C4:K4)