# Tagged Questions

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.

119 views

### 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 ...
4k views

### 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 ...
3k views

### 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 A:A --------- 1 11 111 22 2 ...
708 views

### 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. ...
12k views

### 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 ...
1k views

### How to use countif, sumif across two sheets where the logical test is an equivalency?

I am building a simple sum sheet that relies on a conditional but I can't seem to get COUNTIF/SUMIF to work for me. To sum, I have two sheets, one with a few hundred lines of records including an ...
285 views

### How do I count multiple words available in sentences and multiple rows (countif, search)?

here is screenshot for the sheet. https://flic.kr/p/qZb8ot in column A there are some senteces list below: A2. T-shirts, Varsity A3. T-shirts, Sticker, Mug A4. T-shirts A5. T-shirts, Pin, ...
11k views

### 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 ...
567 views

### Excel COUNTIF Statement

A---------------------------B 2012-04-12 12:52...... FC 2011-06-24 10:07...... FC 2011-06-24 10:08...... FC 2012-04-17 0:00 ...... COH 2012-04-12 12:22 ...... FC 2011-06-24 10:08.........
3k views

### 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 ...
2k views

### 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?
188 views

### 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 ...
634 views

### 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 ...
1k views

### 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 ...
41 views

### How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?

Using Excel/Calc I have the following situation: a range of cells containing values; assume it's A1:B10, and a string I want to look for; for example, assume it is contained in C1. Now what I want ...
90 views

### Count cells containing all 3 separate words in Excel

I have this excel file containing multiple rows. I wanted to convert the following expression to an excel formula but can't find a reference on how to: COUNTIF(CELL CONTAINS "APPLE" AND CELL CONTAINS ...
32 views

### Excel: conditional COUNTIF?

How can I evolve an =COUNTIF(A:A;"foobar") so that if the cell in column A is empty it looks (for the "foobar") in column B?
931 views

### Excel: Reference Dropdown Cell in Countif Formula

I'm trying to substitute text in an excel formula with a cell reference to the text in a dropdown. First formula that yields the correct answer is: =(SUM(COUNTIFS('LP - All Properties'!G:G,{"Phoenix"...
61 views

### How to use OR in a COUNTIF function when dealing with dates and “>” characters

I want to write a function whereby I could count a cell if it is blank OR greater than a date. This is a piece in what will be a much larger formula so looking to avoid a solution of SUM(countif(D:D,...
158 views

### COUNTIF and SUM in Excel with different ranges

Is there a more efficient way of making the following function?: >=COUNTIF(A1,">0")+COUNTIF(C1,">0")+COUNTIF(E1,">0")+COUNTIF(G1,">0") I want to count if the cell is bigger than 0 ...
278 views

### Excel Sort and Lookup Values Formula

I am trying to create some sort formulas on excel. An example is shown below regarding players and their corresponding scores. I need to query the "Raw Data" and get the "Top 3" players and their ...
495 views

### Excel: How to count cells, whose date values represent a day between monday and friday?

I want to count cells, whose values are dates, under the following condition: The date value entered must be a day between Monday and Friday. Assume the following data and my try: Column A -- 15.01....
31 views

### Excel: Count the number of matches between lists

I have two lists, each on separate tabs. One list occupies B5:B10 (though the number of rows could change) and the other is named "proteins". I want to count the number of matches between the lists (...
318 views

### Advanced data consolidation in Excel

I need to Combine/Consolidate Data dynamically between over 20 separate Excel workbooks. The Column names and their relative position at the top row are the same. The positioning of each word under ...
188 views

### How to count unique numbers based on month?

Column A include a list of numbers, some are duplicated Column B include a list of numbers from 1-12 that represent the months I need to count the unique numbers in column A where the month in ...
45 views

### Custom function COUNTIFUNIQUES in Excel [duplicate]

Possible Duplicate: Excel: how to count number of distinct values in a range? I would like to create a custom function COUNTIFUNIQUES that extends the functionality of COUNTIFS. Here is an ...
55 views

### How does this SUMPRODUCT(COUNTIF( )) function work?

I was searching for a solution to count distinct values in a range, and came across this article. The example provides a list of ten names comprised of four unique entries. It then uses the ...
3k views

### Excel COUNTIF Slow

I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF ...
29 views

### Nested countif's frying my brain

What I want is the following scenario: If all values are "Pass" then display "Passed" If all values are "Fail" then display "Failed" If all values are "Incomplete" then display "Not Started" If all ...
59 views

### Excel counting “A”'s with cell values of “A,B” “R,A” etc

Trying to count the number of individual variable in Excel but Countif doesn't seem to fit my needs For example individual cells have "A","A,B", "A,J", etc and I want to count the number of "A"s - is ...
197 views

### Count matches between two arrays

How can I count the number of entries two arrays have in common, using just standard excel formulae (i.e. no VBA)? Can't find an answer anywhere. e.g. A B +------------- 1| 1 10 2| ...
26k views

### Why doesn't formula work on COPIED data?

I'm using countif formulas across two and three columns in an Excel 2010 spreadsheet. The formulas work perfectly on data that is ENTERED but not on data that is COPIED into the spreadsheet, even ...
311 views

### Excel countifs criteria neither this nor that

Im trying to make a criteria for COUNTIFS such that the value of the cell is neither "pass" nor "fail" nor blank. How can I do this? I dont think "<>pass<>fail<>*" works Since someone wanted ...
1k views

### How can I run SUMIF or COUNTIF with two Conditions?

I recently got some great help with SUMIF and COUNTIF and ended up with the following, working formulas; =SUMIF(REC!\$F:\$F,\$C2,REC!\$G:\$G) =COUNTIF(REC!\$F:\$F,\$C2) I had been mixing up the syntax and ...
37 views

### index and match 2 criteria with COUNTIF

I know many questions have been asked for using INDEX and MATCH with criteria and returning a unique list without duplicates. And I have that much working. But I ran into a problem. Here is my data: ...
88 views

### Need unique count of text from multiple columns by category

My data looks something like (assume begins in A1): Category,Label1,Label2 Dogs,blank,doe Dogs,zoo,blank Cats,zoo,yup Cats,doe,hgg Cats,blank,doe What the result should look like: Dogs,...
66 views

### Functions (specifically text functions) within a countif function

Is there any way to nest text operators within a countif? I want to check if the first 4 characters of a file are in the form A**. =Countif(Left(A1,4),"A??.") doesn't work, but I can put in a =...
463 views

### Excel Countif Function Error [closed]

I need to count values with < and > infront of them but excel won't recognize them as true numbers. How do I use a count function with true values and values?
5k views

### Excel formula to count when value changes?

I have an Excel conundrum I can't seem to crack. In Column A I have a long list of counties, and each of those counties is a member of a larger group of counties that pertain to a particular funding ...
457 views

### Countif incorrectly matches long number [duplicate]

In Excel 2013 or 2010, if you put this value in column B1: 2014022701000400 And then this formula in column C1: =COUNTIF(B1,"2014022701000409") The formula will evaluate to 1. But the numbers ...
1k views

### Excel count all rows that contain certain values for certain fields

I have a sheet with a couple hundred rows. Each row has an 'owner' and a pass/fail field. I am making a second sheet that has statistics for the users. So for each user it will count the number of ...
1k views

### using functions in COUNTIF criteria

Do you of any documentation of Excel regarding specifying complex criteria for COUNTIF - e.g. conditions using functions, referring to neighbor cells, etc. E.g. something like (this one doesn't work) ...
15k views

### CountIf Formula that counts cells containing any and all dates without having a date range?

I need to simplify a CountIF formula. Instead of having a date range I just need my COUNTIF to count cells cointaining dates disregarding date range period. I have tried for example simplifying it ...
27 views

### Count the amount of times a value appears combined with a certain event

I basically have the following list: How can I count the amount of times John won? I have tried using both countif and countifs but have find no positive result. I either get "#VALUE!" or a simple ...
39 views

### How to countif date in a specific column [closed]

how to countifs date in a specific column and to differentiate between start date and end date count.
162 views

### Excel - For each unique ID, every time a particular column turns one, it needs to be cumulatively added

I have a big data set. What I want to happen is for each customer number occurrence, when the transaction type column has an entry CC or SO, it needs to be calculated as a donation. So for each ...
567 views

### How to count cells with values in another sheet's last column?

I want to count cells which are not blank in another sheet's last column. Now I'm using this formula in sheet1: =COUNT(\$'Sheet2'.H11:H54). At the end of every month I add 2 columns to sheet2. ...
127 views

### How to compare and sumproduct two cells value and use multiple criteria in IF in Excel 2013

I want to compare the month value (=TEXT(A1,"MMMMMM")) from sheet A with a cell value (JULY) in sheet B using an IF statement. If the months are same then I need a sumproduct of the cells in sheet A. ...