+ Reply to Thread
Results 1 to 12 of 12

Too few arguments - combination of COUNTIFS and OR

  1. #1
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Question Too few arguments - combination of COUNTIFS and OR

    Hi all,

    I was supposed to do countifs for 3 conditions. But Excel says that I put too few arguments.

    Please Login or Register  to view this content.
    The count will only be done if:
    1. Either B9:B32 or B39:B62 or both more than 50
    2. If B69:B92 more than H6 in other sheet

    Not sure which part that is wrong in here. Mind to help? Thanks!

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Too few arguments - combination of COUNTIFS and OR

    You're nesting the logic wrong.

    Please Login or Register  to view this content.
    something like that; as it is, COUNTIFS is being fed an OR where it expects the first range.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Too few arguments - combination of COUNTIFS and OR

    I believe your formula is a little bit wrong? Because my condition is supposed to be either B9:B32 or B39:B62 or even both must be more than 50, instead of B9:B32 > 50 and B39:B62 is lesser than 50. That is why I wanted to incorporate OR actually.

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Too few arguments - combination of COUNTIFS and OR

    In that case, COUNTIFS probably isn't the best tool. I mean, you could probably do it with an array construction, but at that point....

    Please Login or Register  to view this content.
    I think the Boolean SUMPRODUCT construction is a better plan of attack.


    The Less Than thing was a typo LOL

  5. #5
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Too few arguments - combination of COUNTIFS and OR

    Lol I see. What's "--" in here? The sheet name or....?

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Too few arguments - combination of COUNTIFS and OR

    No it's to force the SUMPRODUCT to assess the Booleans as numbers. (You also see people use +0 or *1 to do the same thing).

    Uuuh... here's an explanation of what in blazes I'm doing.

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,358

    Re: Too few arguments - combination of COUNTIFS and OR

    Or:

    Please Login or Register  to view this content.
    Quang PT

  8. #8
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Too few arguments - combination of COUNTIFS and OR

    Quote Originally Posted by ben_hensel View Post
    In that case, COUNTIFS probably isn't the best tool. I mean, you could probably do it with an array construction, but at that point....

    Please Login or Register  to view this content.
    Ben, how can I alter your formula to exclude the countif of every hour if the data every hour shows "Bad"? I have tried your formula but it also counts the cells which have "Bad" in it.

  9. #9
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Too few arguments - combination of COUNTIFS and OR

    Quote Originally Posted by bebo021999 View Post
    Or:

    Please Login or Register  to view this content.
    Hi Bebo, I believe your formula will count twice if both B9:B32 and B39:B62 are more than 50 right?

  10. #10
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Too few arguments - combination of COUNTIFS and OR

    Please Login or Register  to view this content.
    The logic there is a bit twisty -- it returns one (so TRUE), unless the range has "Bad" in it. Functionally, it compares to IF(unit="Bad", 0, 1)

    You can add another AND to the SUMPRODUCT construction just by giving it another range to multiply against -- if there's a zero anywhere in a product, it's zero, so it's Boolean AND.

    That (range>50)+(range>50)>0 in the first range of the SUMPRODUCT is an ANDOR test, where at least one of them has to be TRUE to return TRUE.
    XOR would be (range>50)+(range>50)=1 , where you want one-and-only-one TRUE in the XOR, for the XOR to return TRUE.

  11. #11
    Forum Contributor
    Join Date
    09-19-2017
    Location
    London
    MS-Off Ver
    2015
    Posts
    169

    Re: Too few arguments - combination of COUNTIFS and OR

    So, let's say if I want to add another condition of AND, I can put it like this:

    Please Login or Register  to view this content.
    hence in this way, both condition will have to be obeyed right?

  12. #12
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Too few arguments - combination of COUNTIFS and OR

    Yes, but since "B6=B9" is a reference to two cells, not two ranges, it will be TRUE or FALSE for the whole function -- it's the same as IF(B6=B9, SUMPRODUCT(), 0)

+ 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. SUBTOTAL and COUNTIFS combination
    By aaron_burr in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-07-2020, 12:52 AM
  2. Adding date range to SumIf/CountIfs combination
    By lans4rd in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-12-2015, 05:21 PM
  3. COUNTIFS - Too Few Arguments
    By meppwc in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-17-2015, 10:22 AM
  4. [SOLVED] COUNTIFS - To few arguments
    By GSAN in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-02-2014, 10:51 PM
  5. VBA: Using Evaluate / Countifs Combination to Compare Dates
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-08-2013, 02:02 PM
  6. Vba: Using Evaluate Sumproduct / Countifs combination for Date
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2012, 07:44 PM
  7. Storing CountIfs arguments in an array?
    By fomoz in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-17-2011, 05:07 PM

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