Using IF and COUNTIF together I have this formula working for just the one cell but I would like it to work for everything the the column from A24:A1500 and J24:J1500. But for some reason when I change it to a range it errors out. In Excel 2010: I would like to create a formula which would count values from COLUMN E depending on information in columns B and G of the same row. If, in column B, value is M (male) and in column E is YES, than it would count.
The Function COUNT Explained:Count Cells In Excel (Easily)Written by co-founder, Microsoft Office Specialist.Of all the things that Excel does, counting seems rather rudimentary.But below the surface, there’s so much more to it!I guarantee you’re going to be surprised by the power of Excel’s COUNT function ?And when you get into COUNT’s sister functions, there are some really cool things you can do.Let’s take a look at COUNT, and then get into how you might use this function—and related functions—to get more valuable data from your spreadsheet. Let’s take a look at an example to see how COUNT could be useful. Our example worksheet contains a short set of survey results.Question #2 asked respondents to rate their satisfaction with a product.Most people responded with numbers, but a few responded with text. Let’s use COUNT to figure out how many people submitted numerical responses.First, click into cell F2 and type “Number of numerical ratings Q2:” so we don’t forget what we used this function for.Next, click into G3 type “=COUNT(” to start the formula. If you have duplicate values in your data, and you want to find out how many unique values there are, COUNTIF can help with that too. It just needs a little help from SUMPRODUCT.Let’s start with the syntax for counting unique values.
In our example workbook, we’ll count the number of unique values in column A to find out how many different people took the survey.Here’s the formula we’ll use:=SUMPRODUCT(1/COUNTIF(range, range))We’ll start on the inside. COUNTIF, when given a range both as the range and criterion, returns a series of values in an array. By using each value as the divisor in a fraction, and using SUMPRODUCT to combine those fractions, we get the number of unique values in a range.Let’s run this formula on the Name column. COUNT seems like a rather simple request of Excel at first, but once you see the power of the counting functions, you’ll find all sorts of uses for them.Whether you’re seeing how many items are in a list, counting specific cases, or figuring out the number of unique values, Excel’s counting functions can help.And when you combine them with other functions, COUNT, COUNTA, and COUNTIF can do some heavy lifting! Think about where you might put them to use in your own work, and you’ll certainly come up with some great uses.
COUNTIF formula in Excel counts the numbers of cells in a range that meet the given condition. COUNTIF Formula SyntaxCOUNTIF Formula has two parts:COUNTIF (range, criteria)rangerange is the range in which the value that you are trying to find out is located.
Criteriacriteria is the value whose occurence in the above range we would like to count. The criteria can be a string (Ex “Bill”), a number (Ex.
15) or a combined expression (“=”&15). Example of a COUNTIF FormulaUsing COUNTIF to find the number of times a string appears in a rangeLet’s look at an example of the COUNTIF formula. Suppose we wanted to find out the number of time a particular value (“Bill Wanton”) appears in the range as shown in the example above. We could simply write =COUNTIF(A2:A10,F2). The result in this case will be 4 as shown in the example above.
Using COUNTIF to find the number of times a string appears in a range using the wildcard characterNow if we wanted to find the number of times a name starting with “T” appears in the range. The formula =COUNTIF(A2:A10,”T.”) would have done the trick here. Using COUNTIF as an Array FormulaNow suppose we wanted to know the number of times each name appears in the range. The simple way to do is to simply write =COUNTIF($A$2:$A$10,A2) in an adjacent column. However if we wanted to, we would have used the array formula and entered it as =COUNTIF(A2:A10,A2:A10) with a CTRL+SHIFT+ENTER. (See for greater details) Using COUNTIF to find the number of times a numeric value appears in a rangeWe have seen the example of the above where we try to find out the occurrence of a single string. The formula pretty much stays the same if you want to count the number of times a numeric value appears in the range.
Say you wanted to find the number of times sales have been less than 100 units. You could find that out using =COUNTIF(D2:D10,””&F2) where the cell F2 contains the name of the manager. This way, whenever the value of cell F2 changes, the formula returns the new value. How to enter the COUNTIF formula in an Excel Sheet1.
Select the cell in which you want to place the formula2. Type the formula as =COUNTIF(3. Then using the keyboard up-down and left-right arrow key, move the cursor to the first (top-left) cell of the range in which we would like to count the occurrence of a string.4.
Keeping the SHIFT button pressed, move the cursor to the last cell (bottom-right) of the range.4. Press the comma key (,)5.
Then either enter the value you would like to know the frequency of appearance of – if its a string enclose in quotes (Ex. “Bill’), if its a number enter as such, if its an expression enter it within quotes (Ex.