AVERAGEIFS across two sheet range

[kumara_faith]

Hi,

I have the following input data in sheet 1:

Expense Tracker.xlsx
	B	C	D	E
1	Category	INR	RM	MYR/INR
2	Total Fund	INR 40,000	RM2,297.40	₹ 17.41
3	Total Expense	INR 400	RM22.97
4	Daily Average	INR 200	RM7.66
5	Balance	INR 39,600	RM2,274.42
6
7	Date	Expense	Amount (INR)	Amount (RM)
8	Saturday, 29 October, 2022	Accommodation	250	14
9	Sunday, 30 October, 2022	Biscuit	150	9
10
11
12
13
Expense Journal

Cell Formulas
Range	Formula
D2:D3,D5	D2	=C2/$E$2
E2	E2	=E1.Price
C3	C3	=SUM(D8:D1000)
C4	C4	=AVERAGE(SUMIFS(D8:D200,B8:B200,UNIQUE(FILTER(B8:B200,B8:B200<>""))))
D4	D4	=Forecast!C4/$E$2
C5	C5	=C2-C3
E8:E13	E8	=IF(D8="","",D8/$E$2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C5	Cell Value	>0	text	NO
C5	Cell Value	<0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B8:B356	List	=Date
C8:C470	List	=Expense1

In sheet 2, I have another inout table as well:

Expense Tracker.xlsx
	B	C	D	E
7	Date	Expense	Amount (INR)	Amount (RM)
8	Tuesday, 1 November, 2022	Lunch	150	9
9	Wednesday, 2 November, 2022	Lunch	140	8
10
11
Forecast

Cell Formulas
Range	Formula
B7:E7	B7	='Expense Journal'!B7
E8:E11	E8	=IF(D8="","",D8/$E$2)

Cells with Data Validation
Cell	Allow	Criteria
B8:B459	List	=Date
C8:C459	List	=Expense1

I am trying to expand the AVERAGEIF formula in cell C4 from sheet 1 to include the entries in sheet 2 as well. The correct results will then be 172.5. Is there a way to accomplish this ? Appreciate the help.

 

[Automatrix]

You can simulate an AVERAGEIF with SUMIF/COUNTIF. So it could be (SUMIF(sheet1)+SUMIF(sheet2))/(COUNTIF(sheet1)+COUNTIF(sheet2)).

 

[kumara_faith]

Hi Auto,

Thank you. How would that incorporate the unique dates? The current formula calculates the average of the daily expense based on unique dates.

 

[Automatrix]

Then it's probably SUM(UNIQUE(FILTER))) for each of them, divided by COUNT(UNIQUE(FILTER)))).

 

[kumara_faith]

Hi,

I tried as follows but its not working:

=AVERAGE(SUM(UNIQUE(FILTER('Expense Journal'!B8:B369,'Expense Journal'!B8:B369<>""))))+AVERAGE(SUM(UNIQUE(FILTER(B8:B369,B8:B369<>""))))/(COUNT(UNIQUE(FILTER('Expense Journal'!B8:B369))))+COUNT(UNIQUE(FILTER(B8:B369))))

 

[Automatrix]

Thinking deeper about it, this method will count some days twice, so it may not work. Your best bet may be to combine the two sheets using either the VSTACK function (which may only work on 365 online excel) or - my personal favorite - Power Query.

 

[kumara_faith]

HI,

I have a cross post here:

Expand Average Formula with Another Sheet

Hi, I have the current formula in cell C4 in sheet 1 in input data (Expense Journal) which calculates the average expense daily based on the unique dates. =AVERAGE(SUMIFS(D8:D200,B8:B200,UNIQUE(FILTER(B8:B200,B8:B200<>"")))) I also have the same format of input data in sheet 2...

www.excelforum.com

 

[Fluff]

How about

=LET(vs,VSTACK(B8:D200,Forecast!B8:D200),f,FILTER(vs,INDEX(vs,,1)<>""),AVERAGE(BYROW(UNIQUE(INDEX(f,,1)),LAMBDA(br,SUM(IF(INDEX(f,,1)=br,INDEX(f,,3),0))))))

 

[kumara_faith]

Hi Fluff,

Thank you for the solution and that worked. Appreciate your patience and have a wonderful day ahead.

 

[Fluff]

Glad to help & thanks for the feedback