A CHOOSEIF or LISTIF equivalent of SUMIF

[TMLstan]

I have a Nightly Report with sales data entered in rows and days of the week in columns. For simplicity sake, lets say column 1 lists the sales data (SALES in A2, COGS in A3, LABOR in A4 and OP PROFIT in A5) and the days of the week are in columns B through H with column I as WTD.

There are actually ten of these reports with a separate tab for each store.

I want to create a tab with a new report, a side-by-side comparison of the stores daily performance for the most current day. The same sales data line items as the store reports in Column A2:A4 but Stores 1 through 10 in columns B through K and a total in column L.

What I cannot figure out is a formula to pick the most current days data from each of the store reports for each line item, essentially the equivalent of a “SUMIF” formula for the CHOOSE function if that makes sense.

I would appreciate any help.

 

[Beyond_avarice]

You could use an index/match: where your column match value will link to a cell referencing a date to view.

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

Also, that you investigate XL2BB for providing small 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.


Something like this? Note that this does require the labels in column A to be on identical rows in all worksheets.

TMLstan 2020-03-23 1.xlsm
	A	B	C	D	E	F
1		MON	TUE	WED	THU	FRI
2	SALES	5	8	1
3	COGS	5	1	1
4	LABOR	7	5	9
5	OP PROFIT	4	3	5
Store 1

TMLstan 2020-03-23 1.xlsm
	A	B	C	D	E	F
1		MON	TUE	WED	THU	FRI
2	SALES	1	7	6
3	COGS	3	7	8
4	LABOR	5	3	7
5	OP PROFIT	8	7	2
Store 2

TMLstan 2020-03-23 1.xlsm
	A	B	C	D	E	F
1		MON	TUE	WED	THU	FRI
2	SALES	1	3	5
3	COGS	2	9	12
4	LABOR	8	7	5
5	OP PROFIT	2	12	2
Store 3

Formula in B2 is copied across and down.

TMLstan 2020-03-23 1.xlsm
	A	B	C	D
1		Store 1	Store 2	Store 3
2	SALES	1	6	5
3	COGS	1	8	12
4	LABOR	9	7	5
5	OP PROFIT	5	2	2
Summary

Cell Formulas
Range	Formula
B2:D5	B2	=LOOKUP(10^10,INDIRECT("'"&B$1&"'!$B"&ROW()&":$H"&ROW()))

 

[TMLstan]

Updated my profile. Thank you for making me aware of that.

I'm going to need a few hours to digest and understand your suggestion. It looks good though!

Thanks for the quick reply.

 

[Peter_SSs]

Updated my profile. Thank you for making me aware of that.

Thanks for the quick reply.

You're welcome on both counts.
And thank you for updating your profile.

I'm going to need a few hours to digest and understand your suggestion. It looks good though!

Post back with details it it needs some tweaking.