+ Reply to Thread
Results 1 to 2 of 2

Count differences in Values between 2 columns in a table, across multiple worksheets

  1. #1
    Registered User
    Join Date
    05-11-2018
    Location
    UK
    MS-Off Ver
    2003
    Posts
    4

    Count differences in Values between 2 columns in a table, across multiple worksheets

    Hi Everyone,

    I'm hoping someone can help me out with an issue I'm having;

    I have a workbook which has the same table repeated on 10 sheets, the data in each is different.

    I currently use INDIRECT with a tab list range to create a sumproduct countifs to count various factors across all tables and this works perfectly, however I have hit a roadblock.

    I need to count the number of differences between 2 columns in each table, I could construct 10 countifs and add them all together but I would rather use the approach above as if I need to add or remove sheets in the future it will be easier to amend.

    Here is an example of my current formula:

    =SUMPRODUCT(COUNTIFS(INDIRECT(""&TabList&"[Logistics Service Provider]"),"<>Supplier DDP",INDIRECT(""&TabList&"[Reason Code]"),"<>4. Incorrectly Reported by Region",INDIRECT(""&TabList&"[Delivered Time]"),<>INDIRECT(""&TabList&"[Booked Time]")))

    The last element of the above countifs is the one I am having trouble with, I need to count the instances that appear in Delivered Time that don't match with corresponding value in Booked Time.

    Any help would be appreciated

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,429

    Re: Count differences in Values between 2 columns in a table, across multiple worksheets

    I propose putting all of the tables on one sheet and using hyperlink.
    Instead of jumping from table to table using sheet tabs, use the hyperlink function to move between tables on the worksheet. This may make your formula easier as well as improve the performance of the workbook, since it reduces the number of volatile functions (INDIRECT).
    Note that the attached file will need to be saved before the hyperlinks (A1:A4) will work.
    Note that when you upload the file the name will likely be changed to Book11(hyperlink test)(1).xlsx remove the (1) and any extra spaces before saving.
    I suggest two things.
    1. Upload a desensitized sample of your workbook (not a picture or pasted copy) by clicking on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
    2. Update your profile. COUNTIFS was not introduced until the 2007 version and it makes a difference in what proposals are offered.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. Replies: 6
    Last Post: 08-19-2015, 07:46 PM
  2. Replies: 0
    Last Post: 07-17-2014, 10:06 AM
  3. Function to count differences between two columns
    By mdoherty83 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-23-2012, 09:17 AM
  4. [SOLVED] Compare columns in different worksheets nad highlight differences
    By intercooler in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-13-2012, 01:05 PM
  5. compare two columns and count the differences
    By tdstephens1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-25-2012, 12:04 PM
  6. Replies: 5
    Last Post: 04-21-2011, 05:22 PM
  7. Replies: 0
    Last Post: 01-29-2010, 06:40 PM

Tags for this Thread

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