Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-07-2018, 05:14 PM
Ricardo Sousa Ricardo Sousa is offline Table with values from several locations and years - need to find comp values Windows 10 Table with values from several locations and years - need to find comp values Office 2016
Novice
Table with values from several locations and years - need to find comp values
 
Join Date: Jun 2018
Posts: 7
Ricardo Sousa is on a distinguished road
Default Table with values from several locations and years - need to find comp values

Dear All,
Please see table below.


As you can see the file has “sales” (column H) from different location by day, week number and year.


My question is:
Is it possible to create a formula at the column I that gives the “sales” from the comparable day last year?
Example: on the first line I would like to have in column I the value of the “sales” from the Sunday of week 22 of 2017 (the “sales” from that day are at column H.

Thanks very much for your help
Ricardo
Attached Images
File Type: png ricardo.png (78.1 KB, 18 views)

Last edited by Ricardo Sousa; 06-08-2018 at 08:00 AM. Reason: Inserting picture of table
Reply With Quote
  #2  
Old 06-08-2018, 12:04 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Table with values from several locations and years - need to find comp values Windows 7 64bit Table with values from several locations and years - need to find comp values Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi and welcome
as all members cannot access external links please post your sheet on the forum (click Go advanced - Manage attachments). Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 06-08-2018, 08:01 AM
Ricardo Sousa Ricardo Sousa is offline Table with values from several locations and years - need to find comp values Windows 10 Table with values from several locations and years - need to find comp values Office 2016
Novice
Table with values from several locations and years - need to find comp values
 
Join Date: Jun 2018
Posts: 7
Ricardo Sousa is on a distinguished road
Default

Thanks very much for your tip
Reply With Quote
  #4  
Old 06-08-2018, 01:20 PM
ArviLaanemets ArviLaanemets is offline Table with values from several locations and years - need to find comp values Windows 8 Table with values from several locations and years - need to find comp values Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

As it looks like you have defined a Table, and assuming for a location you have not more than 1 row on same day (on fly):
Code:
=SUMIFS([Ext. Price],[Name],[@Name],[day week],[@[day week]],[week],[@week],[year],[@year]-1)
Reply With Quote
  #5  
Old 06-09-2018, 03:31 AM
Ricardo Sousa Ricardo Sousa is offline Table with values from several locations and years - need to find comp values Windows 10 Table with values from several locations and years - need to find comp values Office 2016
Novice
Table with values from several locations and years - need to find comp values
 
Join Date: Jun 2018
Posts: 7
Ricardo Sousa is on a distinguished road
Default

Hi Arviaanemets,
this is brilliant. completely solved my issue.

Thanks very much for your help
Reply With Quote
  #6  
Old 06-09-2018, 07:35 PM
Ricardo Sousa Ricardo Sousa is offline Table with values from several locations and years - need to find comp values Windows 10 Table with values from several locations and years - need to find comp values Office 2016
Novice
Table with values from several locations and years - need to find comp values
 
Join Date: Jun 2018
Posts: 7
Ricardo Sousa is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
As it looks like you have defined a Table, and assuming for a location you have not more than 1 row on same day (on fly):
Code:
=SUMIFS([Ext. Price],[Name],[@Name],[day week],[@[day week]],[week],[@week],[year],[@year]-1)
Hi ArviLaanemets,

the tip you gave me was FANTASTIC, but now i have another issue

with that table i created 3 pivots - one for week sales, one for monthly sales and one for year to date sales.
the question is:
do you know a way to have the monthly and year to date to display like for like numbers?

Again thanks very much for your help.
Reply With Quote
  #7  
Old 06-09-2018, 10:51 PM
ArviLaanemets ArviLaanemets is offline Table with values from several locations and years - need to find comp values Windows 8 Table with values from several locations and years - need to find comp values Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by Ricardo Sousa View Post
the question is:
do you know a way to have the monthly and year to date to display like for like numbers?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find matching values between 2 docs sam1234 Word 3 11-27-2017 04:05 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:03 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft