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

1. ## 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.

2. ## 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. ## Re: Multiple Criteria search and COUNT FOR TEXT across multiple sheet ARRAY's

please find the attached file for reference....

4. ## 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. ## 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

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

#### 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