+ Reply to Thread
Results 1 to 10 of 10

using a countif function nested within a sumproduct function

  1. #1
    Registered User
    Join Date
    04-19-2012
    Location
    Hamilton, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    26

    using a countif function nested within a sumproduct function

    I have a formula which is a countif function nested within a sumproduct which counts the number of threatened species from a list occurring in a particular cell.

    The fomula is =SUMPRODUCT(COUNTIF(B2,"*"&$G$2:$G$9&"*"))

    where G2 to G9 is the list of threatened species.
    Column B contains the site's information which mentions what species are present at each site.

    What I would like to know is how does this formula work?

    Cheers
    Attached Files Attached Files
    Last edited by helium; 07-01-2012 at 11:02 PM.

  2. #2
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: using a countif function nested within a sumproduct function

    have you tried using the Evaluate Formula functionality to figure out what it is doing?

    if you have not, you can get to that function this way - ALT + M + V.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  3. #3
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: using a countif function nested within a sumproduct function

    This would do the same thing

    =SUMPRODUCT(--(ISNUMBER(SEARCH($G$2:$G$9,B2))))

    Maybe that's easier to understand?

  4. #4
    Registered User
    Join Date
    04-19-2012
    Location
    Hamilton, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: using a countif function nested within a sumproduct function

    Thanks icestationzbra and Cutter. I'm more familiar with search nested within isnumber as I often use it as a wildcard.

    I'll less knowledgeable about sumproduct and also what does the -- symbol mean?

  5. #5
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: using a countif function nested within a sumproduct function

    It's called a double unary and is a way to coerce numeric values from their TRUE/FALSE values.

  6. #6
    Registered User
    Join Date
    04-19-2012
    Location
    Hamilton, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: using a countif function nested within a sumproduct function

    Thanks! Do you know of any good resources where I could learn about this?

  7. #7
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: using a countif function nested within a sumproduct function

    you are there ;-)

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: using a countif function nested within a sumproduct function

    Here is a great resource
    HTH
    Regards, Jeff

  9. #9
    Registered User
    Join Date
    04-19-2012
    Location
    Hamilton, New Zealand
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: using a countif function nested within a sumproduct function

    Excellent! thanks

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: using a countif function nested within a sumproduct function

    Glad it helps...

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

+ 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