+ Reply to Thread
Results 1 to 3 of 3

complex query

  1. #1
    Forum Contributor
    Join Date
    12-16-2006
    Posts
    349

    Unhappy complex query

    I need a formula to check down coloumn "I" for anyone called "Simon Gibbs"

    and then check down coloumn "E" for any rows with "1" or "2" or "3" or "4" or "5" or "6" or "7" or "8" or "9" or "10"

    and see how many rows that have simon gibbs and anyone these numbers in

  2. #2
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    to count Simon Gibbs in column I:
    =COUNTIF($I:$I,"Simon Gibbs")
    - or -
    =COUNTIF($I:$I,"*Simon Gibbs*")

    To count positive integers 1 through 10 in column E:
    =SUMPRODUCT(--(ABS($E$1:$E$65000)=$E$1:$E$65000),--(INT($E$1:$E$65000)=$E$1:$E$65000),--($E$1:$E$65000<=10),--($E1:$E65000>=1))

  3. #3
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

    =SUMPRODUCT(--($I$2:$I$100="Simon Gibbs"),--ISNUMBER(MATCH($E$2:$E$100,{1,2,3,4,5,6,7,8,9},0)))

    or

    =SUMPRODUCT(--($I$2:$I$100=L2),--ISNUMBER(MATCH($E$2:$E$100,$M$2:$M$11,0)))

    ...where L2 contains Simon Gibbs, and M2:M11 contains 1, 2, 3, etc.

    Hope this helps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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