» Using the COUNT Functions
CATEGORY - Excel Counting
VERSION - All Microsoft Excel Versions
How can we count the cells in List1 (column A) according to the following criteria:
1. Cell contains a number.
2. Cell is not empty.
3. Cell is empty.
4. Cell contains a number larger than 5.
5. Cell contains text.
6. Cell contains the string "Excel".
7. Cell contains 3 characters only.
Solution:
Use the COUNT, COUNTA, COUNTBLANK, and COUNTIF functions as shown in the following formulas:
1. =COUNT(A2:A13)
2. =COUNTA(A2:A13)
3. =COUNTBLANK(A2:A13)
4. =COUNTIF(A2:A13,">5")
5. =COUNTIF(A2:A13,"*")
6. =COUNTIF(A2:A13,"*Excel*")
7. =COUNTIF(A2:A13,"???")
To replace the criteria specified in the COUNTIF function with a cell reference containing the desired criteria, include the following operators: <, >, and = within quotation marks and use the "&" operator to join (concatenate) them with the cell reference.
The same method can be applied when using the "*" operator (wildcard) to count cells containing a particular substring.
Thus, with the information stored in C11:C14, the above COUNTIF functions (4-7) could be modified, as follows:
1. =COUNTIF(A2:A13,">"&C11)
2. =COUNTIF(A2:A13,C12)
3. =COUNTIF(A3:A13,"*"&C13&"*")
4. =COUNTIF(A2:A13,C14)
Book Store:
Recommended Books:
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- The Essential 55: An Award-Winning Educator's Rules for Discovering the Successful Student in Every Child
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Lower Your Taxes - Big Time! : Wealth-Building, Tax Reduction Secrets from an IRS Insider
- Mastering Excel 2000 (for beginner)
- Advanced modelling in finance using Excel and VBA
daddylonglegs
Try
=IF(COUNTIF(Pivot!$A$1:$A$5,A1),VLOOKUP(A1, Pivot!$A$1:$B$5,2,FALSE),0)
I have in the past used COUNTIF function and it has worked but now it keeps giving me a #VALUE! - i have tried changing the formats so that all text is TEXT and all numbers are NUMBERS etc. but this doesnt seem to correct the problem.
I think it is something simple but for the life of me cant work out what it is!!
This is the formula I am using, column A is a code (in TEXT) and B is a total in stock.
=IF(COUNTIF(Pivot!$A$1:$B$5,A1),VLOOKUP(Pivot!$A$1:$B$5,A1,2,FALSE),0)
I would appreciate any help.
Thanks in advance
Neil