+ Reply to Thread
Results 1 to 4 of 4

Help Needed - Compare Sheet4 values with Sheet1,sheet2 and sheet3 of excel 2007 version

  1. #1
    Registered User
    Join Date
    05-31-2010
    Location
    cbe, india
    MS-Off Ver
    Excel 2003
    Posts
    12

    Help Needed - Compare Sheet4 values with Sheet1,sheet2 and sheet3 of excel 2007 version

    Friends,

    i need to compare the values of sheet4 with sheet1,sheet2 and sheet3.

    here is what i have tried.

    in sheet4 i have entered the below formula

    =COUNTIF(sheet1!C3:sheet1!C269,C1:C100)

    but the above formula is comparing only sheet1. i dont know how to include sheet2 and sheet3 in the above formula...

    so....

    thanks & regards
    sathyguy

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help Needed - Compare Sheet4 values with Sheet1,sheet2 and sheet3 of excel 2007 versio

    If you're wanting the sum of all the matching conditions you should enter that formula in the same cell (Say A1) in each of sheets 1,2 & 3. The formula doesn't need to contain the sheet name since it will default to the sheet on which the formula exists. So just

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note that the second element you used "C1:C100" is not the usual way of defining the value to be checked. Excel simply assumes you mean the first cell in the C1:C100 range, hence that's all you need specify. So enter this as Sheet4!C1 and in that cell enter the value you're wanting to count.

    In your summary sheet4 in the summation cell enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-31-2010
    Location
    cbe, india
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Help Needed - Compare Sheet4 values with Sheet1,sheet2 and sheet3 of excel 2007 versio

    thanks..but...

    =COUNTIF(C3:C269,Sheet4!C1)

    suppose if any value comes in sheet4 of c3 or c5 or anyother cell of C? then the above formula will work?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Help Needed - Compare Sheet4 values with Sheet1,sheet2 and sheet3 of excel 2007 versio

    As I said using a range in the second parameter of a COUNTIF is redundant since Excel assumes the value of the first cell only.

    Can I suggest you upload the workbook with some real data and manually add the results you expect to see. Clearly note which ranges are original data and which ranges are the results which you have calculated manually.

+ 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. Compare each row in sheet1 to each row in sheet2 -no pair write in sheet3 - HARD
    By VBA-NOOB in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 04-14-2014, 02:14 PM
  2. Replies: 8
    Last Post: 12-22-2013, 01:16 PM
  3. [SOLVED] Need micro compare code in sheet2 and sheet3 with code in sheet1
    By jamalmail in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2013, 05:15 PM
  4. Matching names from sheet2, sheet3, sheet4 with names in sheet1
    By Lynx2x in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-28-2012, 07:51 AM
  5. Macro to copy values from Sheet1 to Sheet3, based on criteria in Sheet2
    By John74 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2010, 04:55 PM
  6. Compare 2 cells on sheet1 with 2 cells on sheet2 and copy unmatched rows to sheet3
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-08-2010, 07:56 AM
  7. Excel 2007 : Compare: Sheet1 - Sheet2 into Sheet3
    By vbjohn in forum Excel General
    Replies: 3
    Last Post: 07-17-2009, 03:33 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