Calculate Total Expense With Condition

[kumara_faith]

Hi,

I have the following table:

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Month	Expense Category	Amount		Today	5/12/2024		Month	Jan	Feb	Mar	Apr	May	June	July	August	September	October	November	December
4	Jan	Loans	5000					Average	45	60	59	43	11
5	Jan	Food	50					Total Actual	5225	5301	5293	5217	5053
6	Jan	Automobile	60					Total Estimated					6041
7	Jan	Medical	80
8	Jan	Food	20
9	Jan	Temple	15
10	Feb	Loans	5000
11	Feb	Food	150
12	Feb	Automobile	65
13	Feb	Medical	45
14	Feb	Food	23
15	Feb	Temple	18
16	Mar	Loans	5000
17	Mar	Food	110
18	Mar	Automobile	65
19	Mar	Medical	78
20	Mar	Food	15
21	Mar	Temple	25
22	Apr	Loans	5000
23	Apr	Food	125
24	Apr	Automobile	25
25	Apr	Medical	17
26	Apr	Food	35
27	Apr	Temple	15
28	May	Loans	5000
29	May	Food	10
30	May	Automobile	12
31	May	Medical	14
32	May	Food	12
33	May	Temple	5
Sheet1

The expense details is in the first table. In the second table, I am trying to calculate the average per month excluding the Loans expenses which is the outlier for every month. The total column includes the loans expense. I am trying to calculate the following:

1. The average expense for every month excluding the Loans in row J4:U4
2. In rowJ6:U6, I am trying to calculate the total expense for the current month based on the average expense for Jan-Apr . This will differ based on the current month. If the current date is June, then it will be Jan-May and so on.

The average expense per day for Jan-Apr excluding Loans is 52. The current date is 12 May 2024 with a balance of 19 days in May. Average expense of 52 X 19 days +5000(Loans) will result in 5988. Assuming total expense for May as of 12 May 2024 excluding Loans is 53, then total expense for May will be 6041

I tried the following but it did not work:

=IFERROR((AVERAGEIFS($D$4:$D$100000, $B$4:$B$100000, "<="&N3, $C$4:$C$100000, "<>"&"Loans") * (EOMONTH(G3,0)-G3+1)) + SUMIFS($D$4:$D$100000, $B$4:$B$100000, N3, $C$4:$C$100000, "Loans"), 0)

Please note that the actual range is from b4:b100000. This is a working file and the expenses will be entered everyday. The formula range will from B4:B100000 and the amount will be in the range d4:d10000. Both range will will have empty values as we enter the data every month from Jan to Dec 2024.



Is there a formula that could be used to accomplish this ? Appreciate all the help.

 

[Cubist]

You're welcome.