+ Reply to Thread
Results 1 to 6 of 6

How do I create a sheet that looks at 2017 and compares with 2018

  1. #1
    Registered User
    Join Date
    01-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    How do I create a sheet that looks at 2017 and compares with 2018

    So I have the following raw data:

    Pic 1.png


    Is there a way of getting excel to make the sheet look like this so that multiple company entries are shown as a number count and also get excel to sum the amount in the spend column.

    Pic 2.png

    The excel doc is attached now (sorry about that!)


    Many thanks in advance
    Attached Files Attached Files
    Last edited by Dcoulthard; 07-18-2018 at 04:30 PM. Reason: attachment no added

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,093

    Re: How do I create a sheet that looks at 2017 and compares with 2018

    I don't think you are going to find too many takers who are willing to re-create your worksheet for you. Some issues can be explained adequately with a picture, but most can't.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    01-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How do I create a sheet that looks at 2017 and compares with 2018

    Thanks for that, I have now added the attachment.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,589

    Re: How do I create a sheet that looks at 2017 and compares with 2018

    I dont agree with some of your answers...
    eg you have a count of 1 for Company2 for 2017 but it has 0 spend?

    but this does what you want...
    =COUNTIFS('Data Before'!$A$2:$A$7,$A2,'Data Before'!$B$2:$B$7,">0")

    If you need to add VOID...
    =IF(COUNTIFS('Data Before'!$A$2:$A$7,$A2,'Data Before'!$B$2:$B$7,">0")=0,"VOID",COUNTIFS('Data Before'!$A$2:$A$7,$A2,'Data Before'!$B$2:$B$7,">0"))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    01-16-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: How do I create a sheet that looks at 2017 and compares with 2018

    The Count column was to count how many times a company is listed in the sheet and then the spend column is to count up how much they have spent. As sometimes the companies are listed multiple times, so hence still showing a 1 for company 2 in 2017 but its spend that year was 0.

    Thanks for the countifs

    Is there a way of getting excel to say that:

    if company 3 appears in 2017 then add up all its spend for that year (one separate column on the data afterwards sheet)
    if company 3 appears in 2018 then add up all its spend for that year (Another column)

    and

    if there is a "VOID" in the count column in 2017 then mark the spend as VOID
    if there is a "VOID" in the count column in 2018 then mark the spend as VOID


    Thanks in advance
    Last edited by Dcoulthard; 07-18-2018 at 06:54 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,589

    Re: How do I create a sheet that looks at 2017 and compares with 2018

    OK looks like I neglected to add the SUM formula to my previous post - sorry

    Amend my COUNT formula to this
    =IF(COUNTIFS('Data Before'!$A$2:$A$7,$A2)=0,"VOID",COUNTIFS('Data Before'!$A$2:$A$7,$A2))

    and for the sum...
    =SUMIFS('Data Before'!$B$2:$B$7,'Data Before'!$A$2:$A$7,$A2,'Data Before'!$B$2:$B$7,">0")
    and I still say that at least 1 of your sample answers is nor correct (you have 2017 Comp4 = 5, when it should be 20)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] HOW TO COUNT NUMBER OF Months in a Column starting from 2017 till 2018
    By Shahbazk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-22-2018, 04:00 AM
  2. Imported text 12/8/2017 becomes 8/12/2017
    By terrypin in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 02-20-2018, 02:46 PM
  3. [SOLVED] Amend VBA to add dd-mm-yyyy at end of file name as it show 5-2-2018 instead of 05-02-2018
    By robertguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-05-2018, 10:58 AM
  4. how to sort Week no- Year (04-2018, 05-2019, 07-2018) in pivot chart.
    By sahana108 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-23-2018, 09:07 AM
  5. Fiscal Year 2018 ( Feb 4, 2017 - Feb 3, 2018)
    By chethan1333 in forum Excel General
    Replies: 1
    Last Post: 05-09-2017, 08:40 AM
  6. [SOLVED] How to format dates like 9/25/2017 to 09/25/2017
    By chessmonsta in forum Excel General
    Replies: 11
    Last Post: 03-17-2017, 03:11 AM
  7. Replies: 20
    Last Post: 10-19-2016, 06:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1