+ Reply to Thread
Results 1 to 5 of 5

Combining CountIfS Formulas

  1. #1
    Registered User
    Join Date
    08-21-2013
    Location
    Jacksonville, FL, USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Combining CountIfS Formulas

    The formula =COUNTIFS(D8:D19,"=apples",F8:F19,"=green") results in an answer of 2.
    The formula =COUNTIFS(D8:D19,"=peppers",F8:F19,"=green") results in an answer of 3.

    How to combine these formulii to get an answer of 5?

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Combining CountIfS Formulas

    Try this version

    =SUM(COUNTIFS(D8:D19,{"apples","peppers"},F8:F19,"green"))
    Audere est facere

  3. #3
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Combining CountIfS Formulas

    You can try any of these functions

    =SUMPRODUCT((D8:D19="Apples")*(D8:D19="Peppers")*(F8:F19="Green"))
    =SUMPRODUCT(--(D8:D19="Apples"),--(D8:D19="Peppers"),--(F8:F19="Green"))
    Please click 'Add reputation', if my answer helped you.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Combining CountIfS Formulas

    Those formulas will always return zero ramananhrm, because like COUNTIFS the SUMPRODUCT function with * works like an "AND", so D8:D19 would have to be both "apples" and "peppers" at the same time which isn't possible, for an OR version with SUMPRODUCT you'd need this syntax or similar:

    =SUMPRODUCT((D8:D19="Apples")+(D8:D19="Peppers"),(F8:F19="Green")+0)

  5. #5
    Registered User
    Join Date
    08-21-2013
    Location
    Jacksonville, FL, USA
    MS-Off Ver
    Excel 2010
    Posts
    61

    Re: Combining CountIfS Formulas

    Daddy Longlegs, The SUM formula works exactly as needed. Thanks for your help. R/Phil
    Last edited by Phil Hageman; 08-21-2013 at 09:31 AM.

+ 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] Combining COUNTIFs with SUM IF FREQUENCY MATCH to work out a total
    By acillatem in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-27-2013, 03:36 AM
  2. Combining COUNTIFS and OR function
    By tatyanamarie in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-12-2013, 08:51 AM
  3. Formula Optimization on several Countifs formulas
    By dcgrove in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-18-2013, 02:31 PM
  4. [SOLVED] Combining two COUNTIFS
    By fletch8701 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-19-2012, 11:07 PM
  5. Replies: 0
    Last Post: 12-16-2011, 09:01 AM

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