Super User
Super User

Questions tagged [countif]

COUNTIF generally refers to a worksheet function within a spreadsheet program that counts cells in a range based on a specified condition. This tag can also be used for COUNTIFS, which provides a similar function but allows multiple ranges and criteria.

Syntax

=COUNTIF(range, criteria)

range (required) - The group of cells you want to count. Range can contain numbers, arrays, or references that contain numbers. Blank and text values are ignored.

Criteria (required) - A number, expression, cell reference, or text string that determines which cells will be counted.

Microsoft KB for countif

Microsoft KB for countifs

Open Office countif documentation

204 questions
6
votes
3 answers

Count unique value ignoring Text in range

123 562 My Data 548 K166 You 1548 123 562 100 My Data 100 I want to count unique values by ignoring text in the above shown list. For this I've tried an array formula, {=SUM(1/Countif(,,,,))} but it's not working properly. Can anyone suggest a…
Rajesh Sinha
  • 9,403
5
votes
3 answers

Excel Countif 0, 00, 000 separate entries

I want to count 0, 00 and 000 in my data which is formatted as TEXT but the countif() formula gives me same value for each of the three search criteria. Please find the attached image for more details
ABR
  • 51
5
votes
2 answers

Excel: Counting number of MAX values in a range

I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet: A B C D 1 Round | Rita | Sue | Bob ------+------+-----+----- 2 1 | 5 | 3 | 4 3 2 | 2 | 2 | 5 4 3 | 3 | 1 | …
Shevek
  • 16,738
4
votes
1 answer

Excel countif doesn't work with numbers

in the following example you get output '0' for a strange reason. Can someone explain why this occurs and what I can do about it? (The format of the cells is "text") rows…
Bulki
  • 190
  • 1
  • 7
4
votes
2 answers

check two conditions in two different columns in excel and count the matches

I've trying to create a Error Log to help me analyse my mistakes. So for simplicity, lets assume I have two columns "Type of Question" - with values SC,RC,CR and another column that indicates whether I got this question "right/wrong".Let's assume…
user1727103
4
votes
2 answers

excel countif filtered by style

I have an Excel spreadsheet and I'm trying to count rows in a column based on a style applied to some cells in the column. Is there a simple way to do this?
Bill Weinman
  • 208
  • 1
  • 2
  • 6
4
votes
4 answers

How to evaluate multiple OR'd conditions in COUNTIFS (or similar) row-by-row?

Consider the following sample data. A B 1 2 8 4 2 1 3 7 4 8 Separately, the count of A that is less than 5 is =COUNTIF(A1:A5, "<5") -> 4, and that for B is =COUNTIF(B1:B5, "<5") -> 3. I know if I want the count of rows where A…
Drake
  • 105
3
votes
2 answers

Count first occurrence of one criterion based on a second criterion

I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. To give you some context, each Person…
Dre
  • 143
3
votes
3 answers

Include an OR condition in a COUNTIF?

This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me. I have a column that can contain 0, 1 or more valid tokens as well as 0, 1 or more invalid…
Tom Wright
  • 553
3
votes
2 answers

Display the lines that meet the COUNTIF criteria

Not sure if this is possible but when using the COUNTIF function is it possible to actually find the lines that meet the criteria. For example I have a COUNTIF formula that looks at a large amount of data and comes back with the result of '5' So 5…
Geotaz
  • 41
3
votes
1 answer

=countif says "0001234" is not equal to "0001234" How to force countif to treat strings as text not numbers?

I have some product codes, some of which are numbers with leading zeroes, stored in a spreadsheet. I'm doing some operations that rely on a COUNTIF not equal to function (details in comments but not relevant to the question). Countif seems to break…
Some_Guy
  • 794
2
votes
1 answer

Excel: count number of unique matches for multiple criteria

I have sheet1 with a list of people and their allergies/dietary requirements. There are nine allergies/dietary needs, and each person has 1 or more allergies. Each requirement is a column, each person in a row. If the person has that allergy, the…
thelawnet
  • 91
2
votes
4 answers

Using COUNTIF in Excel to count number of "Greater than" values in an array of values

So here is my situation. I'm a principal creating a tracker for my teachers to use to track student growth on assessments. In the current tracker it is set up like this: % of 67% Students Met **Student Name …
Brendan
  • 21
2
votes
1 answer

MS Excel: Count frequency of similar values in between blank cells

I'd like the count the frequency of identical values in a row. The value must occupy at least two cells and must be beside each other. I'd like to post the image here but i do not have enough reputations. Anyway, screenshot can be found here:…
Mark Robles
  • 23
2
votes
1 answer

Count value between 2 date ranges

I was able to count how many occurrences of dates between 2 date ranges, however now I having difficulty counting how many times Yes occurs between 2 date ranges. this formula works for counting how many times a date range occurs where I23 and I24…
tcole
  • 23
1
2 3
…
13 14