+ Reply to Thread
Results 1 to 10 of 10

Countifs with Boolean Values given by sets of conditions

  1. #1
    Registered User
    Join Date
    11-05-2014
    Location
    Lisbon, Portugal
    MS-Off Ver
    2013
    Posts
    10

    Countifs with Boolean Values given by sets of conditions

    So, here's the puzzle:

    I have 2 set of conditions i want to see met, and count the times both of them are true.

    When I write =countifs(A1:A20="Pizza",True,B1:B20="Pepperonni",True), Excel doesn't even allow me to check results. Instead, it simply demands that I insert ' in the beginning of the formula, because it doesn't recognize what I wrote as a formula.

    If however I replace the values in A1:A20 and B1:B20 with trues and falses, the formula works. In other words, when I write =countifs(A1:A20;True;B1:B20;True) the value returned is correct.

    I want these conditions to be embedded in the formula and not in cells, because I want to do multiple variations of conditions and this simply isn't practical.

    Anyone out there as an idea of what might be causing excel to freak when I embed the conditions in the countifs?

    Thanks a million in advance.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Countifs with Boolean Values given by sets of conditions

    I'm not sure where the Pizza and Pepperoni come into it - what values do you have in columns A and B? Perhaps you can attach a sample workbook (the FAQ describes how to).

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Countifs with Boolean Values given by sets of conditions

    Hi,

    a "standard" countifs could be


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A little sample file could help

    Regards

    Edit: sorry Pete_UK: did not mean to overlap.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Countifs with Boolean Values given by sets of conditions

    hI tRY

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Boo?ath?

  5. #5
    Registered User
    Join Date
    11-05-2014
    Location
    Lisbon, Portugal
    MS-Off Ver
    2013
    Posts
    10

    Re: Countifs with Boolean Values given by sets of conditions

    Sorry I've given so little info, just realized I may have oversimplified the problem a bit too much.

    I'm attaching an example of what I intend to do. I wish to know how many telephone orders we've gotten.

    Keep in mind that the same order will have several entries in the database. I've included a formula that I think is near to what I intended.

    Now I just need Excel to accept it.

    Excel Example 1.xlsx
    Last edited by NunoE; 11-05-2014 at 08:10 AM.

  6. #6
    Registered User
    Join Date
    11-05-2014
    Location
    Lisbon, Portugal
    MS-Off Ver
    2013
    Posts
    10

    Re: Countifs with Boolean Values given by sets of conditions

    Just check Example 1, ignore the others

  7. #7
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Countifs with Boolean Values given by sets of conditions

    Hi, try

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Countifs with Boolean Values given by sets of conditions

    Hi,

    an attempt

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Cheers

  9. #9
    Registered User
    Join Date
    11-05-2014
    Location
    Lisbon, Portugal
    MS-Off Ver
    2013
    Posts
    10

    Re: Countifs with Boolean Values given by sets of conditions

    My mind was just blown. Why... How... I don't get how that works, but it does. Thanks guys.

  10. #10
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Countifs with Boolean Values given by sets of conditions

    Hi thanks for the feedback , If your Query Solved, You can mark the thread as solved and (preferably) say thanks to those who helped by clicking the Add Reputation button.

+ 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. #DIV!/0 error when using two sets of countifs
    By guerinto in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-26-2014, 04:50 PM
  2. [SOLVED] Countifs, Boolean <= & >=, Text or Value
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-20-2013, 06:58 PM
  3. Boolean values in charts
    By general_belgrano in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-13-2013, 10:35 AM
  4. [SOLVED] boolean function to check if multiple conditions are met
    By edjohns in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2012, 11:26 AM
  5. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) problem
    By 2funny4words in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2009, 07:28 PM

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