+ Reply to Thread
Results 1 to 5 of 5

Month Criteria in Countifs ????

  1. #1
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Smile Month Criteria in Countifs ????

    Hi there,


    I'm trying to use Excel 2007 and got struck with Countifs Function.......................
    Please check out the COUNTIFS function on Data Tables Sheet -> Cell D29 and pls. let me know what's going on..
    What I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan-10" under a particular person selected from B 27 cell (Data tables tab).

    I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ........
    =COUNTIFS('System Data'!C2:C6020,">=01-01-10",'System Data'!C2:C6020,"<=31-01-10",'System Data'!D2:D6020,B27,'System Data'!E2:E6020,"Pending")

    Although i've solved it by using sumproduct formula in which i used month(D2:D100)=month(B13)
    =SUMPRODUCT(--('System Data'!$D$2:$D$130=$B$12)*--('System Data'!$E$2:$E$130=$A14)*--(MONTH('System Data'!$C$2:$C$130)=MONTH(B$13)),--(YEAR('System Data'!$C$2:$C$130)=YEAR(B$13)))
    but I am unable to use this month function in Countifs, May be there is no facility of range function in Countif ?????

    In other words can we use this formula which i used in sumproduct ???????

    =COUNTIFS(month('System Data'!C2:C6020),month(Cell reference),year(cell reference))


    Appreciate your help ....
    Attached Files Attached Files
    Last edited by mubashir aziz; 02-11-2010 at 07:03 AM. Reason: Thanks a lot DK for his valuable suggestions .....
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Month Criteria in Countifs ????

    Quote Originally Posted by ma
    I am unable to use this month function in Countifs
    Correct.

    COUNTIFS is really no different from COUNTIF in so far as you can not adapt the values being evaluated as part of the calculation (unlike SUMPRODUCT)

    Applying a MONTH function to a date is an adaptation of the underlying value - therefore not possible with COUNTIF(S)
    (remember you'd have to test YEAR also)

    Quote Originally Posted by ma
    I solved it by using >=01-01-10 and <=31-01-10 criteria but i don't think its nice idea ....
    It might not be a nice idea but using COUNTIFS it is the only viable approach
    (obviously you could make the dates adapt per the headers)

    In summary, based on your sample file...

    COUNTIFS

    Please Login or Register  to view this content.

    SUMPRODUCT

    Please Login or Register  to view this content.

    or if you wish to conduct one date based date (and account for possibility of non-numerics)

    Please Login or Register  to view this content.

    goes without saying the SUMPRODUCTs will be slower.

    Given the volume of data... if you want a really quick approach... I would say you'd be better off adding a "key" to System Data tab, eg:

    Please Login or Register  to view this content.

    You can then use a much (much much) more efficient COUNTIF test given you now have only one condition (based on Key column), eg:

    Please Login or Register  to view this content.

    The final route - though involving more formulas - will be significantly quicker to calculate than the SUMPRODUCT approaches - and still quicker than the SUMIFS approach.

  3. #3
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Month Criteria in Countifs ????

    Thanks a lot DK as i came here after so many months and waiting for your valuable advise ....... Now Countifs concept is clear and I"ll adopt Sumproduct approach ..........
    cheers

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Month Criteria in Countifs ????

    Quote Originally Posted by ma
    Now Countifs concept is clear and I"ll adopt Sumproduct approach ..........
    Why ?

    I'm obviously not very good at explaining things... of all the approaches outlined SUMPRODUCT is the least efficient... never forgo efficiency for elegance.

    To be clear... in order of preference:

    1 - use helper column on System Data sheet and COUNTIF

    2 - if above is not viable use COUNTIFS

    3 - use SUMPRODUCT

    Only if backwards compatibility is a concern and helpers aren't possible should you use SUMPRODUCT in this manner.

    When working out "how bad will this be" ... take one SUMPRODUCT, eg:

    Please Login or Register  to view this content.

    Sum the number of cells in use: roughly 18,000 in the above (3 * 6000)

    multiply that figure by the number of SUMPRODUCTs you intend to use: 75 per your sample file

    the resulting number gives you an idea of how many cells will be "iterated" in generating your matrix output, in this case:

    18,000 * 75 = 1,350,000 cells !

    Arrays & SUMPRODUCT are unique in the way they "process" the calcs which is why they're slower than COUNTIFS / COUNTIF - esp. when used in large volume and/or with large ranges to evaluate (if they're Volatile it's obviously very bad news)

  5. #5
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Month Criteria in Countifs ????

    Thank you very much for your detailed explanations as usual and now I have same to know that why some of my work sheets always takes some time to open. But unfortunately I’ve used some volatile formulas in those sheet as well
    I’ve checked all your above formulas and came to know the speed between the usage of sumproduct and countif ….
    I’ve one very large worksheet and upon creation of that file, I couldn’t use any other formulas except Sumproduct and my that sheet is damn slow. Actually I feel comfortable with sumproduct especially when we are using multiple conditions . Anyhow, I’ll reduce the size and will try to share in forums for some quick tip.
    Anyway again thanks a lot.
    Last edited by mubashir aziz; 02-26-2010 at 05:42 AM.

+ 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