Table with values from several locations and years

ArviLaanemets · #2 06-25-2018, 10:13 PM

Quote:

I need to find the comparable sales for YTD and MTD based on the opening date

I have't a clue what this means!?

Anyway, I try:

I prefer to use SUMIFS(), and as this function uses column references and doesn't allow expressions (like [Qty]*[Ext. Price]) instead, some additional columns are needed (you can hide them).
I assume the result table is a defined Table too.

Into source table (Table1), add columns like
Sum = [@Qty]*[@[Ext. Price]]
TY = AND(Iif(YEAR([@[Opening Date]] = YEAR(TODAY()), [@[Comp Date]], [@[Opening Date]])<[@Date],YEAR([@Date])=YEAR(TODAY()))

In result table, the formula for TY YTD sales will be like

Code:

=SUMIFS(Table1[Sum], Table1[Store],[@Store],Table1[TY], TRUE)

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Table with values from several locations and years - need to find comp values	Ricardo Sousa	Excel	6	06-09-2018 10:51 PM
code to find account and sum up values	Brian13	Excel Programming	2	05-31-2017 09:26 AM
Find and replace multiple values, according to table value - excel formula	EtanM	Excel Programming	3	04-11-2016 01:43 AM
Display multiple values as new values based on selection from template.	MvdB	Excel	2	09-29-2015 08:51 PM
find 2 values i a table to find the right prise	Vibov	Excel	1	01-11-2015 07:25 AM