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

202 questions
Filter by
Sorted by
Tagged with
74 views

### Functions based on multiple criteria

I want to do some count functions based on multiple criteria. I have a sample dataset here: I want to find the number of Males if BD = MCD or FSGS. For this I tried: =COUNTIF(Gender,"M")+...
28 views

### How to get total QTY from two columns, and get QTY based on location and date range

So I have been trying to get an answer to this situation and I have tried a slew of functions from countifs, sumifs and its not working as I had hoped. What I am trying to do: I have two tabs in excel ...
1 vote
16 views

### How to get unique count of values within a specific date range avoiding duplicate values, for a specific location

I am attempting to obtain the sum of a column that has duplicate values, and have those duplicate values counted as 1 instance within a specific date range, that is representing the reporting week. So ...
1 vote
103 views

### How to get unique count of values within a specific date range avoiding duplicate values

I am attempting to obtain the sum of a column that has duplicate values, and have those duplicate values counted as 1 instance within a specific date range, that is representing the reporting week. So ...
17 views

### CountIFs Beyond

I am trying to compile a summary sheet that takes the Employee Name from Row 5, summarizes how many red (2) or yellow (1) are in any given month. I have figured out the COUNTIF for it as a whole, but ...
73 views

### I want to use countif function to get the count of total devices on each ID i have applied the formula but no answer

I want to use COUNTIF() function to get the count of total devices on each ID i have applied the formula but no answer. Refer to attach image for data.
18 views

### Count number of times 2 words appear in cells

can someone help me. I am looking to count how many times 2 words appear in a column full of job titles. For example I want to know how many Programme Managers there are in column D. Job titles in ...
7 views

### Subtract multiple cells if between date with Excel

Trying to subtract three cells (N1-P1-T1) when cell K1 is between two dates (">="&U2 and "<="&V2). I can't seem to figure out how to subtract three cells when the ...
110 views

### Excel: Using COUNTIF + UNIQUE gives generic error "There's a prblem with this formula"

I'm trying to create an excel function that counts the number of unique entries in a range of (potentially merged) cells. The UNIQUE function returns two values for merged cell, one of which is zero. ...
84 views

### Counting the number of times a value appears in a range of columns

Consider the following table which shows a generalised, abstract version of my specific real-world problem (which I will also illustrate). Each "1" or "0" is part of a column ...
• 103
171 views

### In Excel, how do I count the number of rows in a table that pass a filter with AND and OR logic?

In Excel, I have data like this: I want to put together a formula that counts the rows where Col1 is "a" or "b" and Col2 is "c" or "d". I highlighted the rows ...
• 435
68 views

### Count the number of cells in an Excel range using multiple criteria with wildcards

With Excel, when using the COUNTIFS() function with a single criteria, a wildcard value works just fine. Using the example data below, the following functions work as expected with my interesting data....
• 1,505
74 views

### COUNTIF is 0. Two values in the same column

It seems that my formula is not counting 2 different values in the same column. The only calculation that worked was one with identical values. Can you share a method to associate multiple values to ...
25 views

### Conditionally counting the total number of matching rows for different dates

I need to monitor the TOTAL number of active female visitors by date. Tried to generate a formula by myself but failed and now ask your help. Data are as follows: For each date in column A, the ...
• 115
113 views

### Counting number of cells between two variable in anywhere in list in sequence in excel

I am trying to build a formula in excel to pass two text values to match in a list and find number of cells between them. The count needs to go in sequence 1 to 27 and if one variable is at 27 row and ...
• 3
3k views

### COUNTIF using two conditions while ignoring duplicates

I am attempting to create a chart showing the number of BEV/HEV/ICE vehicles bought each year. I know the formula must have two conditions, but I can't figure out how to get it to ignore duplicates or ...
19 views

### Excel COUNTIFS Statement Error

I need to exclude rows in which the cells within 2 columns are the same - for example: B2=NAME, C2=INFORMATION B4=NAME, C4=INFORMATION In the example above, I only want to count the row the first time,...
1 vote
69 views

### Count specific numbers if first column match

I have a table with the following entries: When I want to count the "1" on every line which starts with J I can use the following Formula =COUNTIFS(A1:A5;"J";B1:B5;"1") ...
• 13
114 views

### Date in 1 cell - how do I count the number of dates

I have dates in one cell i.e. 12/05/2023, 05/03/2023, 07/05/2023, is there a formula that I can use to count the number of dates in one cell?
• 1
1 vote
633 views

### IF condition with partial match of an indexed VLOOKUP column - Excel

I have this sample of a spreadsheet to simplify what I'm trying to do: In "Formula 1" I'm using a VLOOKUP function within an IF condition to assign value_if_true (and value_if_false) ...
• 127
559 views

### Excel: How to count how many times a consecutive text value appears

I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row. I got this spreadsheet where I make those schedules but ...
2k views

### How to add range in multiple columns for countif formula?

I have a sheet where I want to count in multiple columns if it meets a single criteria. I am running into issue where if I select multiple columns under Range, it considers it a Criteria after first ...
• 1
246 views

### shorten excel formula to count over multiple tables on multiple sheets

I'm trying to create a data table that breaks down information from a series of activities with young people over a number of sessions. my main sheets have 3 tables for attendance, waiting list and ...
591 views

### 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, "...
• 105
534 views

### Find number of files deleted

I want to know how many files deleted thru batch script. I found a script and modified it but it does not work. Any idea how to fix it. set-location d:\testing -ErrorAction stop # Change ...
• 423
25 views

### How do I count how many times this category appeared only if the value in the column name is not equals to "N/A"

I have these sample data:https://docs.google.com/spreadsheets/d/1TSpuMRktIRAoACH5s1Dk9189NsNpJwjRtNaR5QROZg8/edit?usp=sharing I wanted to count how many times a certain category has appeared only if ...
• 103
168 views

### COUNTIF with formula expression

I have a grid with header names in the first row and first column, e.g.: A B C D A B C D The cells with the same header name in their row and column represent "valid" value for the ...
1k views

### Identify first occurrence of group of repeated values in excel, but groups could be repeated

So I have a column in excel that looks like the first column in this screenshot: Data screenshot The "existing result" column comes from this formula which is easily found from googling: (...
1k views

### Countif with literal array

Why does this formula not work: COUNTIF({1,2,3},2)? I can use both COUNT() and COUNTIF() with a range, but I can replace the range with a literal array only on COUNT, not on COUNTIF. If I use LET to ...
• 113
28 views

### Am I doing this right? Comparing the mean and countIF for the likert-scale results

I have these for the likert scale Likert scale range When I calculated for the mean, it shows a value for 4.3 which means, this will be under the "Agree". But then, if I'll do a COUNTIF, it ...
• 103
1k views

### Excel COUNTIFS where the condition is row-specific

I am trying to do a COUNTIF on the following data where the criteria is row specific and does not link to a specific value or cell. For example, in the following example, I am trying to count the rows ...
293 views

### MS Excel COUNTIF to math equation

For publication, I need to convert the following MS Excel formula to a math equation: COUNTIF(\$A\$2:\$A\$10416,"<2")-COUNTIF(\$A\$2:\$A\$10416,"<1") I use this to count how many ...
1 vote
155 views

### Need to lookup values in row and give the corresponding month in the column

I need a formula that looks up the number of sales in a month and stage and gives you the highlighted cells in the columns. It has to count the first cell that the sales start in and then if you have ...
• 11
428 views

### Implementing COUNTIF function with a changing string criterion

I have a long list of business licenses in Excel. I have a separate list of all the unique names of the licenses and I want to use a COUNTIF function to count how many times each license comes from ...
62 views

### Excel: Count number of duplicates in row for unique records

I'm looking at repeat enrollment data that varies between two text responses "enrolled" and "not enrolled" over 24 columns, each column represents a consecutive term. The dataset ...
• 23
1 vote
847 views

### Counting the number of times a name appears next to a date

I currently have a pivotTable where each row is a date and each column is an employee. The values in the intersection are the sum of the hours worked that day. From another sheet/tab, I would like to ...
71 views

### Excel creating a pivot tablet

enter image description hereI'm trying to create a pivot table so that I can make a Chart to show how many of each item is on the report. However since some are missing multiple we seperate them in ...
1 vote
487 views

### COUNTIF across multiple sheets does not work with SUMPRODUCT

I have the two sheets below. (The example table above is highly simplified for this question) I'm trying to write a formula that adds the total occurrences of A under the year 2017 (Answer is 3). ...
230 views

### Transposed counts for many columns

The input: I have to get the count of the number of '1' and '0' in each column like this: Is there any way in Excel/macro to do this?
• 23
1 vote
297 views

### How do I count multiple levels of referrals (A referred B, B referred C, D & E, E referred F, G, H, I, etc)?

I have a referral system where you get credit for referring other customers. If those customers refer other customers they get some level of credit for those as well. We need to count not only how ...
• 119
198 views

### Distinguish between A* and A in excel

I’m creating a spreadsheet to be used in schools that use various countifs, subtotals etc to look up data. A big problem I have is that I need to distinguish and count separately students on an A* (A ...
• 11
1 vote
497 views

### Counting and sorting a cell in excel

I need help to sort out a list of agencies. I have now gotten the frequency for each agency but I need them to match. Then, I need to sort out this list from Largest to Smallest frequency. That means ...
• 15
1 vote
29 views

### Multiple conditions, if/or statement ask

I need to write a formula based on multiple conditions, that calculates what percentage of data is completed for each entity. I have created an example dataset below. The condition that will change ...
41 views

### I am trying to generate a value count if true, if it falls within a date range

I typed this formula but am getting an error... can anyone help? =COUNTIFS(West!N6:N26,true,[West!C6:C26,>=1/1/21,date range,<=1/31/21])
2k views

### How do I keep my COUNTIF function from counting my column header?

I have a column filled with dates and am trying to count all the cells after a certain date, using COUNTIF(A:A,">"&DATE(xx,xx,xx)). The problem is it keeps including my column header (...
107 views

### what is the mistake in usage of countifs in excel

I am trying to count number of objects category orbit and criteria > 10 here is my excel sheet https://drive.google.com/file/d/17_aKgd5__R3o4mUMy2sJAuIwpk42aEol/view?usp=sharing I used following ...
• 119
7k views

### My Excel COUNTIF function always returns "0"

I am trying to count how many cells do not start with U, D, or R, appear before 15:00 pm and match a certain date (A4). I am using this function: =COUNTIFS(Sheet1!\$B\$2:\$B\$1088,A4,Sheet1!\$C\$2:\$C\$1088,&...
• 1
1 vote
502 views

### MS-EXCEL How to Rank three columns together using countif

I am trying to rank these three columns based on the minimum values in all three simultaneously, using the formula shown in the picture. But somehow, it selects the values that aren't minimum in one ...
• 11