Sum of categories/referenced to table

[SMASS89]

I need a total SUM of each Category in Column C based off of corresponding amounts in Column D. I also need the SUMS for each Category automatically updated to a separate table.

DATE	ITEM	CATEGORY	DEBIT/CREDIT	TOTAL			MAY 16 - JUNE 1
5/16	NET INCOME	NET INCOME	607	$850			CATEGORY	FORECASTED	ACTUAL
5/16	PR CC	CC	(120)	$730			Rent	$250
5/16	RENT	RENT	(200)	$530			Car Insurance/Phone	$75
5/16	CAR/PHONE	CAR/PHONE	(200)	$330			Groceries	$125
5/16	GAS	GAS	(41)	$289			Gas	$150
5/16	PHONE MOUNT	MISC	(15)	$274			Credit Card	$350
5/16	CHUS	MISC	(70)	$204			SUZUKI LOAN	$186
5/17	MCDONALDS	GROCERIES	(5)	$199			MISC	$200
5/17	CREDIT UNION	NET INCOME	200	$399
5/17	GAS	GAS	(55)	$344
5/17	PHONE MOUNT	MISC	(10)	$334
5/18	PUBLIX	GROCERIES	(10)	$324
5/18	CHUS	MISC	(25)	$299
5/18	BOAT GAS	MISC	(20)	$279
5/19	PR CC	CC	(130)	$149
5/19	VAPE JUICE	MISC	(30)	$119
5/20	MCDONALDS	GROCERIES	(7)	$112

<colgroup><col width="49" style="width: 37pt; mso-width-source: userset; mso-width-alt: 1568;"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 3680;"> <col width="100" style="width: 75pt; mso-width-source: userset; mso-width-alt: 3200;"> <col width="132" style="width: 99pt; mso-width-source: userset; mso-width-alt: 4224;"> <col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 1952;"> <col width="72" style="width: 54pt;" span="2"> <col width="148" style="width: 111pt; mso-width-source: userset; mso-width-alt: 4736;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 3552;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 2880;"> <tbody> </tbody>

 

[kweaver]

I would separate the range of May 16 - June 1 into 2 cells (e.g.,, H1 and J1).

Then, this should calculate (adjust the end range accordingly):

=SUMPRODUCT(($A$2:$A$18>=$H$1)*($A$2:$A$18<=$I$1)*(H3=$C$2:$C$18)*$D$2:$D$18)

 

[SMASS89]

Re: Sum of categories/referenced to table - FOR BUDGETS

I ended up finding a formula for this issue. I thought it would be some =IF(.....) formula, it was just a combination "=IF/=SUMIF" formula. I ended up using the following: =IF(C4=C1,"",SUMIF(C:C,C4,D:D))

To set an Actual Category you only have to change the Category Cell referenced in the formula... C3,C4,C5,C6,C7 ETC...

FC1 = CATEGORY (HEADER)
C4 = "INPUT CATEGORY" E.G. "RENT" or "CREDIT CARD"
D:D = AMOUNT/NUMBER AMOUNTS

A B C D E

DATE	ITEM	CATEGORY	DEBIT/CREDIT	TOTAL
5/16	NET INCOME	NET INCOME	607	$850			CATEGORY	FORECASTED	ACTUAL
5/16	PR CC	CC	(120)	$730			Rent	-$250	-$200
5/16	RENT	RENT	(200)	$530			Car Insurance/Phone	-$75	-$200
5/16	CAR/PHONE	CAR/PHONE	(200)	$330			Groceries	-$125	-$44
5/16	GAS	GAS	(41)	$289			Gas	-$150	-$116
5/16	PHONE MOUNT	MISC	(15)	$274			Credit Card	-$350	-$420
5/16	CHUS	MISC	(70)	$204			SUZUKI LOAN	-$186	-$186
5/17	MCDONALDS	GROCERIES	(5)	$199			MISC	-$200	-$190
5/17	CREDIT UNION	NET INCOME	200	$399
5/17	GAS	GAS	(55)	$344
5/17	PHONE MOUNT	MISC	(10)	$334
5/18	PUBLIX	GROCERIES	(10)	$324
5/18	CHUS	MISC	(25)	$299
5/18	BOAT GAS	MISC	(20)	$279
5/19	PR CC	CC	(130)	$149
5/19	VAPE JUICE	MISC	(30)	$119
5/20	MCDONALDS	GROCERIES	(7)	$112
5/20	WENDYS	GROCERIES	(8)	$104
5/21	MCDONALDS	GROCERIES	(7)	$97
5/21	CHUS	MISC	(6)	$91
5/22	MCDONALDS	GROCERIES	(7)	$84
5/23	NET INCOME	NET INCOME	655	$739
5/23	PR CC	CC	(125)	$614
5/23	PR CC	CC	(25)	$589
5/23	CR CC	CC	(20)	$569
5/23	ACORNS	INVESTMENT	(7)	$562
5/23	GAS	GAS	(20)	$542
5/23	CHUS	MISC	(14)	$528
5/24	SUZUKI LOAN	SUZUKI LOAN	(186)	$342

<colgroup><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

I would separate the range of May 16 - June 1 into 2 cells (e.g.,, H1 and J1).

Then, this should calculate (adjust the end range accordingly):

=SUMPRODUCT(($A$2:$A$18>=$H$1)*($A$2:$A$18<=$I$1)*(H3=$C$2:$C$18)*$D$2:$D$18)

 

[RasGhul]

Re: Sum of categories/referenced to table - FOR BUDGETS

Can you use this?

Book1
	A	B	C	D	E	F	G	H	I
1	DATE	ITEM	CATEGORY	DEBIT/CREDIT	TOTAL		Start Date	01-05-19
2	16-05-19	NET INCOME	NET INCOME	607	$850		End Date	01-06-19
3	16-05-19	PR CC	CC	-120	$730		CATEGORY	FORECASTED	ACTUAL
4	16-05-19	RENT	RENT	-200	$530		Rent	$250	-200
5	16-05-19	CAR/PHONE	CAR/PHONE	-200	$330		CAR/PHONE	$75	-200
6	16-05-19	GAS	GAS	-41	$289		Groceries	$125	-22
7	16-05-19	PHONE MOUNT	MISC	-15	$274		Gas	$150	-96
8	16-05-19	CHUS	MISC	-70	$204		CC	$350	-250
9	17-05-19	MCDONALDS	GROCERIES	-5	$199		SUZUKI LOAN	$186	0
10	17-05-19	CREDIT UNION	NET INCOME	200	$399		MISC	$200	-170
11	17-05-19	GAS	GAS	-55	$344
12	17-05-19	PHONE MOUNT	MISC	-10	$334		Expenses		938
13	18-05-19	PUBLIX	GROCERIES	-10	$324		Net Income		807
14	18-05-19	CHUS	MISC	-25	$299
15	18-05-19	BOAT GAS	MISC	-20	$279
16	19-05-19	PR CC	CC	-130	$149
17	19-05-19	VAPE JUICE	MISC	-30	$119
18	20-05-19	MCDONALDS	GROCERIES	-7	$112
Sheet1

Cell Formulas
Range	Formula
I4	=SUMIFS($D$2:$D$18,$A$2:$A$18,">="&$H$1,$A$2:$A$18,"<="&$H$2,$C$2:$C$18,G4)
I12	=ABS(SUM(I4:I10))
I13	=SUMIFS($D$2:$D$18,$A$2:$A$18,">="&$H$1,$A$2:$A$18,"<="&$H$2,$C$2:$C$18,G13)