+ Reply to Thread
Results 1 to 5 of 5

Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's

    Hi friends.... have been stuck with something very trivial...

    Problem - I have 2 Sheets called A) SUMMARY SHEET and B) DATA SHEET

    SUMMARY SHEET - has "CELL D3" = text value ("BANGALORE" for argument sake)
    "CELL D4" = text value ("DELHI" for argument sake)

    DATA SHEET - has "COLOUM A" = multiple occurrences of text (say "Bangalore and Delhi and 10 other text items")

    DATA SHEET - has "COLOUM I" = multiple random entries of text (say for arg...sake - 20 occurrences of Bangalore in COLOUM A only 7 cells corresponding in COLOUM I have some text in them, rest are blank"

    I need a way in which - for all occurrences of text lets say "Bangalore" in "DATA SHEET COLOUM A" which has corresponding value in "DATA SHEET COLOUM I" ----- then COUNT ALL THOSE IN CELLS HAVING TEXT IN COLOUM I and give the result in "SUMMARY SHEET D3"

    EXPECTED RESULT WILL BE LIKE -

    "FOR SUMMARY SHEET D3"will be like------- "DATA SHEET HAS 20 OCCURANCES OF "BANGALORE" AND CORRESPONDING COLOUM I HAS ONLY 7 CELLS HAVING SOME TEXT" ---- THEN D3 WILL HAVE THE COUNT =7

    "FOR SUMMARY SHEET D4"will be like------- "DATA SHEET HAS 50 OCCURANCES OF "DELHI" AND CORRESPONDING COLOUM I HAS ONLY 14 CELLS HAVING SOME TEXT" ---- THEN D4 WILL HAVE THE COUNT =14

    Hope I have been able to communicate my problem.

    Thanks for your help in advance

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,263

    Re: Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's

    You have sample workbook that describe the problem? try to upload it at the forum...

    Thanks

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's

    Thanks for you reply.....

    please find the attached file for reference....
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-12-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's

    Hi guys,

    could anyone figure out how to do this??

    thanks

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

    Re: Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's

    Not sure I understand your examples, I used this, copied down....
    =SUMPRODUCT(('DATA SHEET'!$A$2:$A$20000=D3)*ISTEXT('DATA SHEET'!$I$2:$I$20000))
    change ISTEXT to ISBLANK
    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

+ 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] Multiple Criteria Isnumber Search Array (with Attachment Example)
    By sweetrevelation in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-13-2013, 05:45 AM
  2. Search an Array for a Value with Multiple Criteria
    By NDev in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2013, 07:02 PM
  3. [SOLVED] VBA Array w/multiple search criteria
    By Jeepster325 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-26-2013, 06:07 PM
  4. Multiple criteria search and count
    By txmedic712 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2010, 01:25 PM
  5. search and count based on multiple criteria
    By themax16 in forum Excel General
    Replies: 1
    Last Post: 04-23-2008, 04:47 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