Question Locked

Changing the criteria in Countif, numerous time

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have an excel worksheet which contains course selections, by student. There are approximately 100 course names and I need to count the number of appearances of each. The Countif formula works for this, but I'd like to know if there is a quicker way of creating it for each criteria, rather than typing in each one separately.

3 replies

by thumbsup2

... meaning it has the { and } brackets around it (like what you get if you use the conditional sum wizard), you can select the cell, then place the cursor in the formula bar and highlight the entire formula and copy it... then put the cursor in the cell you want to copy it to and click into the formula bar, then paste and edit just the criteria. Press and hold CTRL+SHIFT when you press the ENTER button.

by ccyr

Thanks for your response. I stumbled upon a way that is even quicker. When I use the Countif, instead of changing the criteria each time, I use a formula such as: =COUNTIF($E$2:$Q$40,A9). Then when I 'fill down' the criteria changes in increments of one. It allows me to do the counts for hundreds of items almost instantly>

This discussion is closed to new replies.