Sumproducts with text and addition/subtraction in different columns

[Angelinamcon]

I need to add these two functions to get an answer in one cell:

=SUMPRODUCT(($G28:G35="Sephora")*$I$28:I35

and

=SUMPRODUCTS(($G28:G35="Sephora")*$J$28:J25

I am trying to get a running total for how much i send on specific products like Sephora. I hav two column as Deposit just incase i return something and a Paid column so i can see how money through the month i am spending for that one thing. so it should be -10, when i do just one sumproduct it does find how much spent or how much refunded.

Thank you!!

		Paid to or Paid From	description	deposit	paid
3/1/24​	Sephora CC	Sephora			-40​	-40​	3111.37​
3/1/24​	Sephora CC	Sephora		30		0​	3111.37​

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

If you are providing sample formulas, please copy/paste them from your Excel or at least be very careful with what you provide as that is all we have to go on. Neither of your posted formulas are valid formulas. Both are missing parentheses, one has a spelling error and one has mis-matched ranges.

In any case, try this formula in row 28 (I think) and copy down

=SUM(FILTER(I$28:J28,G$28:G28="Sephora",0))

 

[Angelinamcon]

so sorry, yes, here is the excel workbook im trying to figure out

Expenses.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
8		Fixed Expenses				January	February	March	April	May	June	July	August	September	October	November	December
9		Expense	Amout		Monthly Income	3591.59	3601.09	3601.09
10		Car payment	535		Interest Income	6.44
11		Citi Card	267		Refunds	0	409
12		Phone	135			$ 3,598.03	$ 4,010.09	$ 3,601.09	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
13		Total	937
14						Savings 2024
15						January	February	March	April	May	June	July	August	September	October	November	December
16					Accelerated Savings	450	350
17		Running Expenses			529 School Savings	15	15
18		Expense	Amount		Roth IRA	100	100
19		Sephora	0			$ 565.00	$ 465.00	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
20		Ulta	#VALUE!
21		Ipsy	#VALUE!			Total Spending 2024
22		Groceries	#VALUE!			January	February	March	April	May	June	July	August	September	October	November	December
23		Baby Boo	#VALUE!
24		Out-to-eat	#VALUE!
25		Credit Cards	#VALUE!							Total to Date	3111.37
26		Other	#VALUE!		Running Expenses 2024
27		Dogs	#VALUE!		Date of Payment	Method of Payment	Paid to OR Paid from	Description	Deposit	Paid	Running Total	Confirm
28		Cat	#VALUE!		2/28/24	Visa	State of CO	Taxes	409		409	409
29					2/29/24	Visa	State of CO	Monthly Income	3601.09		3601.09	4010.09
30					2/29/24	Visa	Credit Cards	CU CO		-150.93	-150.93	3859.16
31					2/29/24	Visa	Groceries	Kings		-55.04	-55.04	3804.12
32					2/29/24	Visa	Rent	Rent		-650	-650	3154.12
33					3/1/24	Visa	Other	Parking		-2.75	-2.75	3151.37
34					3/1/24	Sephora CC	Sephora			-40	-40	3111.37
35					3/1/24	Sephora CC	Sephora				0	3111.37
Sheet1

Cell Formulas
Range	Formula
F12,F19	F12	=SUM([January])
G12,G19	G12	=SUM([February])
H12	H12	=SUM([[March ]])
I12,I19	I12	=SUM([April])
J12,J19	J12	=SUM([May])
K12,K19	K12	=SUM([June])
L12,L19	L12	=SUM([July])
M12,M19	M12	=SUM([August])
N12,N19	N12	=SUM([September])
O12,O19	O12	=SUM([October])
P12,P19	P12	=SUM([November])
Q12	Q12	=SUM([[December ]])
C13	C13	=SUM(C10:C12)
H19	H19	=SUM([March])
Q19	Q19	=SUM([December])
K25	K25	=SUM(Table9[Running Total])
C19:C28	C19	=SUM(FILTER($I28:$J28,G$28:G28="Sephora",0))
K28:K35	K28	=SUBTOTAL(109,INDEX([@[Deposit ]],1):INDEX([@Paid],1))
L29:L35	L29	=L28+[@[Running Total]]

 

[Peter_SSs]

Thanks for the XL2BB sample data. The issue is that your data is in formal Excel tables which we didn't know about before.

Firstly though, a suggestion for the table in E27:L35 in your sample. Instead of manually entering the 409 in the top cell of that final column, try removing all values/formulas in that column then enter this formula in the top cell where currently you have 409

=SUM(INDEX([Running Total],1):[@[Running Total]])

In relation to the formula you originally asked about (starting at C19 in your latest sample), remove the formulas from C19:C28 then enter this formula in C19. Check that the table name in the formula is correct. It should be the name of the table in E27:L35.

=SUM(FILTER(Table2[[Deposit ]:[Paid]],Table2[Paid to OR Paid from]=[@Expense],0))

 

[Angelinamcon]

Thank you!!!!! This is so incredible helpful!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.