Question about Business & Productivity Software

What kind of function are you asking for and what software are you using?

Posted on Feb 17, 2008

COUNTIF(range,criteria)

Counts the number of cells within a range that meet the given condition.

This COUNTIF formula appears to count only the Employees that have the value "F" in the designated container. ie. Female.

Counts the number of cells within a range that meet the given condition.

This COUNTIF formula appears to count only the Employees that have the value "F" in the designated container. ie. Female.

Nov 20, 2013 | Microsoft Excel 2010

In the cell that you
want the result to appear in, enter the appropriate formula from the following
examples.

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*")

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*")

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

on Nov 11, 2013 | Microsoft Excel Business & Productivity...

To reference the first letters in a cell, use the left function. The syntax is =left(cell,#). So, to return the left two letters from cell A1, you use =left(a1,2). You can put that in a cell or incorporate it into some functions.

Jun 12, 2009 | Microsoft Excel for PC

Use the following formula as an array formula, you need to hit "Shift+Ctrl+Enter" after entering the formula. Lets say you have the Name in column "B", the Age in column "C" and the amount paid in column "D". Here is the formula.

=SUM((B2:B7="Abhilash")*(C2:C7>0)*(D2:D7))

It will calculate the amount paid if the name =Abhilash" and the Agr is greater than zero.

=SUM((B2:B7="Abhilash")*(C2:C7>0)*(D2:D7))

It will calculate the amount paid if the name =Abhilash" and the Agr is greater than zero.

Apr 11, 2009 | Microsoft Works 8.0 for PC

The value of C7cannot be found in the range - Hence the error.

Feb 04, 2009 | Microsoft Office Excel 2003 for PC

1024x768
Normal
0
false
false
false
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
These are Excel functions for Lookup tables. The purpose of
Lookup tables is to bring a value to the table, find the closest (or exact)
match, and then return another value.

An example is the federal income tax table. On your tax return you get your gross income and number of dependents, go to the Lookup table, and find your taxable income.

The V in VLOOKUP means that the table is vertical; HLOOKUP has a horizontal orientation.

If you use the Insert Function button in Excel and paste either function, the dialog box will explain each required field separately with examples.

An example is the federal income tax table. On your tax return you get your gross income and number of dependents, go to the Lookup table, and find your taxable income.

The V in VLOOKUP means that the table is vertical; HLOOKUP has a horizontal orientation.

If you use the Insert Function button in Excel and paste either function, the dialog box will explain each required field separately with examples.

Dec 02, 2008 | Microsoft Excel for PC

Here is one way:

In this example, my numbers are in cells a1 through a4. My average is computed with the formula:

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

I'm summing the range of numbers and dividing that by the count of nonzero numbers. With this formula, you'll get an error if there are no nonzero numbers.

In this example, my numbers are in cells a1 through a4. My average is computed with the formula:

=SUM(A1:A4)/COUNTIF(A1:A4,"<>0")

I'm summing the range of numbers and dividing that by the count of nonzero numbers. With this formula, you'll get an error if there are no nonzero numbers.

Dec 30, 2007 | Business & Productivity Software

Try using the SUMIF function for one condition or multiple conditions. Use the SUMIF worksheet function to create a total value for one range based on a value in another range. For example, for every cell in the rage B5:B25 that contains the value "Northwind", the following formula calculates the total for the corresponding cells in the range F5:F25. Formula:

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

=sumif(B5:B25,"Northwind",F5:F25)

Here are SUMIF functions for multiple conditions.

=sum(if((B5:B25="Northwind")*(C5:C25="Western"),F5:F25))

=sum(if((B5:B25="Northwind")+(C5:C25="Western"),F5:F25))

=sum(if(B5:B25="Northwind",IF(C5:C25="Western"),1,0)))

Also check your help for SUMIF functions.

If this helps you, Please rate me. Thanks, Patty

Dec 09, 2007 | Business & Productivity Software

You may want COUNTIF if you're specifying criteria. For instance, if my prices are found in b3 to b7, here's a formula that will find all those that are less than 6 ($6.00):

=COUNTIF(B3:B7, "<6")

If you're using multiple criteria, such as you want to find all the prices that are greater than $5 and less than $8, the following will accomplish it. (The ABS gives you the absolute value of the result, in case the smaller number is first.)

=ABS(SUM(COUNTIF(B3:B7, ">5") - COUNTIF(B3:B7, "<8")))

=COUNTIF(B3:B7, "<6")

If you're using multiple criteria, such as you want to find all the prices that are greater than $5 and less than $8, the following will accomplish it. (The ABS gives you the absolute value of the result, in case the smaller number is first.)

=ABS(SUM(COUNTIF(B3:B7, ">5") - COUNTIF(B3:B7, "<8")))

Nov 06, 2007 | Oracle 10g Database Standard (ODBSEONUPP0)

I love vlookup!
Suppose you have 1 worksheet with song numbers and titles in Row 1, Cols A:B:
Song# Title
123 Love Me Tender
234 Blue Suede Shoes
345 Dixie
Another worksheet has song number and performer in Row 1, Cols A:B
Song# Performer
123 Elvis Presley
234 Carl Perkins
456 Cher
Notice there is NO performer for song number 345 in the 2nd worksheet.
Now in the 1st work sheet, cell C2 insert this LOOKUP function: =LOOKUP(A2,Sheet2!A:B)
Copy that cell to row 3 and row 4 in Col C. You should get a Performer for all songs even though there is not a song number 345 in the performer worksheet.
Help me out Mr. VLOOKUP.
Insert this VLOOKUP function in cell C2 of the first worksheet: =VLOOKUP(A2,Sheet2!A:B,2,0)
Copy that cell to row 3 and row 4 Col C. You should get the performer names for the 1st 2 songs, but not for 345 Dixie. The result should be #N/A.
That means VLOOKUP could not find a DIRECT match for song 345 in the second worksheet.
That is why I prefer VLOOKUP over LOOKUP.
I have found this explaination of the VLOOKUP parameters helpful:
1. Needle (A2)
2. Haystack (Sheet2!A:B)
3. RELATIVE Col containing result (2)
4. Need DIRECT MATCH ONLY (0)
Hope this helps.
Let me know if you have any questions.

Aug 27, 2007 | Microsoft Office Standard for PC

132 people viewed this question

Usually answered in minutes!

i need to count the same values in one column with vlookup formula and count formula...

for details :

I have one column that has many numbers in each rows and it's possible it has a repeat number...then I want to count the sum of the repeat numbers into another column.. maybe using vlookup and count formula... how can i get it?

×