+ Reply to Thread
Results 1 to 12 of 12

Count individual entries with 2 criteria

  1. #1
    Registered User
    Join Date
    10-30-2019
    Location
    Bucharest
    MS-Off Ver
    365 ProPlus
    Posts
    15

    Count individual entries with 2 criteria

    Hello again, Excellent people!

    I`m having some problems with counting individual entries in a huge table [120969 rows / 20 columns and counting].
    I`m trying to extract individual entries from a specific month (month \ year varies) (and then year), where the entries are tied with another column.
    [how many individual vehicles (A:A) does a company [C:C] has for specified month/year [B:B])

    Unique formula doesn`t seem to work on my MS 365 ProPlus, so I've tried SUMPRODUCT and other examples from the web.

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

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


    These formulas should work, but I`m doing something wrong apparently.
    I have attached a scaled down version of the table. Any help would be greatly appreciated.

    Thank you!
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,996

    Re: Count individual entries with 2 criteria

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

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


    Try above formula
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    10-30-2019
    Location
    Bucharest
    MS-Off Ver
    365 ProPlus
    Posts
    15

    Re: Count individual entries with 2 criteria

    Formula seems to be working fine on the scaled down table, but when applying it to the large table it returns 0,00 and all active workbooks function as if on a Pentium 2 that runs on solar power on the dark side of the moon. Guess I`ll have to try something else or scrap this one.
    Thanks for the prompt reply!

  4. #4
    Registered User
    Join Date
    10-30-2019
    Location
    Bucharest
    MS-Off Ver
    365 ProPlus
    Posts
    15

    Re: Count individual entries with 2 criteria

    Me again!
    Finally managed to adapt samba_ravi`s formula (Thanks again!) to my table, but it takes 2-5-10 minutes to calculate something and every opened Workbook freezes or stops responding.
    I work on a i5 - 7500 3.40Ghz with 16 GB RAM so this shouldn't happen.
    Anyone knows what's wrong?

    Here is the updated formula, with more criteria, just in case someone else needs it:

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


    I guess this thread is Solved and can be closed, if no other replies come my way.

    Thanks!

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    7,996

    Re: Count individual entries with 2 criteria

    are you open to use helper columns?

  6. #6
    Registered User
    Join Date
    10-30-2019
    Location
    Bucharest
    MS-Off Ver
    365 ProPlus
    Posts
    15

    Re: Count individual entries with 2 criteria

    Yes! I use the "big table" just to extract data to other Sheets.

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

    Re: Count individual entries with 2 criteria

    Is it are you looking for?
    Employ 2 helpers for 2 formula.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    10-30-2019
    Location
    Bucharest
    MS-Off Ver
    365 ProPlus
    Posts
    15

    Re: Count individual entries with 2 criteria

    Thank you for the reply and the formulas, bebo021999!
    As in samba_ravi's case, the formulas are working fine, but I am unable to extend them to the table, as it keeps crashing from "lack of memory".
    I can`t go MSO 64 bit and 120979 rows x 2 helpers and one additional Sheet full of complex formulas, seems to be a far to difficult task for my i5 and it`s 16 GB of RAM.
    I`ll keep trying to adapt what you two have provided and hope for the best.
    My thanks!

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,000

    Re: Count individual entries with 2 criteria

    Give a try.
    Code for UDF CountSpecial

    Please Login or Register  to view this content.
    How to Use UDF code:
    In Tthe developer tab click--> Visual Basic
    VB window opens
    Insert--> Module
    Paste the code.
    Close the window.
    Now UDF is available in Function List
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Registered User
    Join Date
    10-30-2019
    Location
    Bucharest
    MS-Off Ver
    365 ProPlus
    Posts
    15

    Re: Count individual entries with 2 criteria

    Hello, kvsrinivasamurthy! Thanks for the reply!
    Unfortunately for now I`m confined to a laptop that can`t open the big file, so I can't test your solution.
    F3 should be =3, because there are 3 unique entries in A:A for G2, that have dates in B:B after value in F2 (1.01.2020 + 12 months). *ILO had 3 cars from 1.01.2020 until today, but only 2 in October (10.2020). So, E3 should count one B101AAA and one B110AAA whileF3 should add one B102AAA.
    Thank you again! Stay safe!

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,000

    Re: Count individual entries with 2 criteria

    I have added E2 and F2 cells . Start date and end date given, it will be easy for getting Formula.

    Code is revised
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,000

    Re: Count individual entries with 2 criteria

    Normal formula approach is also given.

    In E13 then copied To F13

    Please Login or Register  to view this content.
    Attached Files Attached Files

+ 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] Count individual entries in a list
    By DaveFaulstich in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2014, 03:55 PM
  2. [SOLVED] Count how many individual numbers occur based on other criteria in Excel 2003
    By Ash Ford in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-18-2013, 09:03 AM
  3. Count unique entries with criteria
    By Leopold2000 in forum Excel General
    Replies: 9
    Last Post: 12-04-2012, 12:24 AM
  4. [SOLVED] I Need to Count Number of Entries Based on Two Criteria
    By CLR in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 04:05 AM
  5. I Need to Count Number of Entries Based on Two Criteria
    By Jones in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  6. count unique entries with criteria
    By shinbar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-03-2005, 02:31 PM
  7. Count entries when two criteria are met
    By SueJB in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2005, 12:06 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