# CountIF

1. ## 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.

2. ## re: CountIF

Can you upload an example workbook?

3. ## 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. ## 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. ## re: CountIF

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

6. ## 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.

7. ## 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. ## 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.

9. ## 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. ## 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. ## Re: CountIF

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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