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.


[TABLE="width: 713"]
<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>[TR]
[TD="width: 49, bgcolor: #1CFF88"]DATE[/TD]
[TD="width: 115, bgcolor: #1CFF88"]ITEM[/TD]
[TD="width: 100, bgcolor: #1CFF88"]CATEGORY[/TD]
[TD="width: 132, bgcolor: #1CFF88"]DEBIT/CREDIT[/TD]
[TD="width: 61, bgcolor: #1CFF88"]TOTAL[/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"][/TD]
[TD="width: 349, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=5BD2FF]#5BD2FF[/URL] , colspan: 3"]MAY 16 - JUNE 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/16[/TD]
[TD="bgcolor: transparent"]NET INCOME[/TD]
[TD="bgcolor: transparent"]NET INCOME[/TD]
[TD="bgcolor: transparent"]607 [/TD]
[TD="bgcolor: #1CFF88"]$850[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #91E0FF"]CATEGORY[/TD]
[TD="bgcolor: #91E0FF"]FORECASTED[/TD]
[TD="bgcolor: #91E0FF"]ACTUAL[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/16[/TD]
[TD="bgcolor: transparent"]PR CC[/TD]
[TD="bgcolor: transparent"]CC[/TD]
[TD="bgcolor: transparent"](120)[/TD]
[TD="bgcolor: #1CFF88"]$730[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Rent[/TD]
[TD="bgcolor: transparent"]$250[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/16[/TD]
[TD="bgcolor: transparent"]RENT[/TD]
[TD="bgcolor: transparent"]RENT[/TD]
[TD="bgcolor: transparent"](200)[/TD]
[TD="bgcolor: #1CFF88"]$530[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Car Insurance/Phone[/TD]
[TD="bgcolor: transparent"]$75[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/16[/TD]
[TD="bgcolor: transparent"]CAR/PHONE[/TD]
[TD="bgcolor: transparent"]CAR/PHONE[/TD]
[TD="bgcolor: transparent"](200)[/TD]
[TD="bgcolor: #1CFF88"]$330[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Groceries[/TD]
[TD="bgcolor: transparent"]$125[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/16[/TD]
[TD="bgcolor: transparent"]GAS[/TD]
[TD="bgcolor: transparent"]GAS[/TD]
[TD="bgcolor: transparent"](41)[/TD]
[TD="bgcolor: #1CFF88"]$289[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Gas[/TD]
[TD="bgcolor: transparent"]$150[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/16[/TD]
[TD="bgcolor: transparent"]PHONE MOUNT[/TD]
[TD="bgcolor: transparent"]MISC[/TD]
[TD="bgcolor: transparent"](15)[/TD]
[TD="bgcolor: #1CFF88"]$274[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Credit Card[/TD]
[TD="bgcolor: transparent"]$350[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/16[/TD]
[TD="bgcolor: transparent"]CHUS[/TD]
[TD="bgcolor: transparent"]MISC[/TD]
[TD="bgcolor: transparent"](70)[/TD]
[TD="bgcolor: #1CFF88"]$204[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]SUZUKI LOAN[/TD]
[TD="bgcolor: transparent"]$186[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/17[/TD]
[TD="bgcolor: transparent"]MCDONALDS[/TD]
[TD="bgcolor: transparent"]GROCERIES[/TD]
[TD="bgcolor: transparent"](5)[/TD]
[TD="bgcolor: #1CFF88"]$199[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]MISC[/TD]
[TD="bgcolor: transparent"]$200[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/17[/TD]
[TD="bgcolor: transparent"]CREDIT UNION[/TD]
[TD="bgcolor: transparent"]NET INCOME[/TD]
[TD="bgcolor: transparent"]200 [/TD]
[TD="bgcolor: #1CFF88"]$399[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/17[/TD]
[TD="bgcolor: transparent"]GAS[/TD]
[TD="bgcolor: transparent"]GAS[/TD]
[TD="bgcolor: transparent"](55)[/TD]
[TD="bgcolor: #1CFF88"]$344[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/17[/TD]
[TD="bgcolor: transparent"]PHONE MOUNT[/TD]
[TD="bgcolor: transparent"]MISC[/TD]
[TD="bgcolor: transparent"](10)[/TD]
[TD="bgcolor: #1CFF88"]$334[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/18[/TD]
[TD="bgcolor: transparent"]PUBLIX[/TD]
[TD="bgcolor: transparent"]GROCERIES[/TD]
[TD="bgcolor: transparent"](10)[/TD]
[TD="bgcolor: #1CFF88"]$324[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/18[/TD]
[TD="bgcolor: transparent"]CHUS[/TD]
[TD="bgcolor: transparent"]MISC[/TD]
[TD="bgcolor: transparent"](25)[/TD]
[TD="bgcolor: #1CFF88"]$299[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/18[/TD]
[TD="bgcolor: transparent"]BOAT GAS[/TD]
[TD="bgcolor: transparent"]MISC[/TD]
[TD="bgcolor: transparent"](20)[/TD]
[TD="bgcolor: #1CFF88"]$279[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19[/TD]
[TD="bgcolor: transparent"]PR CC[/TD]
[TD="bgcolor: transparent"]CC[/TD]
[TD="bgcolor: transparent"](130)[/TD]
[TD="bgcolor: #1CFF88"]$149[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/19[/TD]
[TD="bgcolor: transparent"]VAPE JUICE[/TD]
[TD="bgcolor: transparent"]MISC[/TD]
[TD="bgcolor: transparent"](30)[/TD]
[TD="bgcolor: #1CFF88"]$119[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5/20[/TD]
[TD="bgcolor: transparent"]MCDONALDS[/TD]
[TD="bgcolor: transparent"]GROCERIES[/TD]
[TD="bgcolor: transparent"](7)[/TD]
[TD="bgcolor: #1CFF88"]$112[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

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

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)