+ Reply to Thread
Results 1 to 11 of 11

CountIF

  1. #1
    Registered User
    Join Date
    05-18-2009
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    5

    CountIF

    Hi everyone,

    As you might have noticed this is my first post here and I feel rather ashamed that it is about an often asked topic. But here it goes:

    I have a number of very large data files (minimum 10000k rows). I would like to count the number of people that fit various criteria. I have already done something about it but need to add another criterion and I am kindda stuck. I cannot install any outside programs since I do not have admin privileges to the computer I am working with.

    Here is the formula that I am currently using:
    =COUNT(AZ10702:AZ21190)-COUNTIF(AZ10702:AZ21190,"<1")-COUNTIF(AZ10702:AZ21190,">10")
    I have 10 more equations like this.
    Now the problem is that I have another variable in column AF and I would like to only count the rows where AF = 3. How can I add this to my current setup? I was desperate enough to delete all rows where AF did not equal 3 but I have 27 data files and it is going to take too long.

    Any suggestions would be much appreciated!

    I should note that I am stuck to using an older version of excel at work either 2002 or 2003.
    Last edited by Shair; 05-18-2009 at 01:01 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    re: CountIF

    Can you upload an example workbook?

  3. #3
    Registered User
    Join Date
    05-18-2009
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    5

    re: CountIF

    Since I do not own the data (only using it for work) I'd rather not unless I have to. Sorry if that sounds rather rude

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    re: CountIF

    Don't put original data!

    But example (you can use part of it like AAAA99 ABABABA100-1) would be helpfull for finding best solution.

  5. #5
    Registered User
    Join Date
    12-19-2008
    Location
    CA
    Posts
    64

    re: CountIF

    Can you make a fake example with 10-30 lines of fake information?

  6. #6
    Registered User
    Join Date
    05-18-2009
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    5

    re: CountIF

    Will do

    AZ (Range between 1 and 99, 99 is improbable and is used for "not applicable")
    8
    23
    36
    45
    99
    99 (I do not count the 99s)
    33

    My criterion for AZ are like the following:
    1 to 10
    11 to 20
    46 to 50
    51 to 60
    61+


    AF (There are only a few seperate numbers in this column, I think 3, 4, 9 and a few others)
    4
    3
    3
    5
    9

    All the rows that I count in the criterion listed in AZ must equal to 3 in AF.

    I hope that clears it up somewhat. The fake workbook is on its way as well.
    Last edited by Shair; 05-18-2009 at 01:02 PM.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    re: CountIF

    That's better... But it would be even easier if you put them in excel table and uplad

    (speaking for next time )

    Here, try this formula:

    =SUMPRODUCT(--($AF$1:$AF$35=3);--($AZ$1:$AZ$35>=10);--($AZ$1:$AZ$35<30))

    numbers 3,10 and 30 you can change as you need or point to some other cells like:

    =SUMPRODUCT(--($AF$1:$AF$35=AL2);--($AZ$1:$AZ$35>=AM2);--($AZ$1:$AZ$35<AN2))

  8. #8
    Registered User
    Join Date
    05-18-2009
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    5

    re: CountIF

    I couldn't quite get that one to work. I keep getting 0. But on the bright side, I did make a fake workbook. So the question now is how I would count those rows that are between 1 and 10 in column G and equal to 3 in column H.
    Attached Files Attached Files

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    re: CountIF

    As I write above, only need to adopt numbers and range you need.

    Copy this:

    =SUMPRODUCT(--($H$2:$H$20=3);--($G$2:$G$20>=1);--($G$2:$G$20<10))

  10. #10
    Registered User
    Join Date
    05-18-2009
    Location
    Rotterdam
    MS-Off Ver
    Excel 2003
    Posts
    5

    re: CountIF

    Yes i figured it out, it was my fault, I forgot to change ;s with ,s. Got it to work now. Thank you very much. This will save me a lot of time.

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: CountIF

    Sorry, I usually mention it but I see you are from Holland so I thought you might have ; settings already

+ 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