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)

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