# 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

0
votes

3
answers

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")+...

0
votes

1
answer

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

1
answer

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

1
answer

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

0
votes

0
answers

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

0
votes

1
answer

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.

0
votes

1
answer

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

0
votes

0
answers

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

0
votes

1
answer

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

0
votes

2
answers

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

0
votes

3
answers

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

0
votes

1
answer

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

0
votes

1
answer

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

0
votes

1
answer

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

0
votes

1
answer

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

0
votes

2
answers

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

0
votes

1
answer

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

1
answer

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")
...

0
votes

1
answer

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
vote

2
answers

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

-1
votes

1
answer

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

0
votes

1
answer

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

0
votes

1
answer

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

4
votes

4
answers

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, "...

0
votes

1
answer

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

0
votes

1
answer

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

0
votes

2
answers

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

0
votes

1
answer

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:
(...

0
votes

1
answer

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

-1
votes

1
answer

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

0
votes

3
answers

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

0
votes

1
answer

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

1
answer

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

0
votes

1
answer

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

2
votes

1
answer

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

1
vote

1
answer

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

0
votes

1
answer

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

1
answer

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

2
votes

4
answers

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?

1
vote

1
answer

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

0
votes

3
answers

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

1
vote

1
answer

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

1
vote

0
answers

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

0
votes

1
answer

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])

0
votes

1
answer

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

0
votes

1
answer

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

0
votes

3
answers

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
vote

2
answers

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

0
votes

3
answers

3k
views

### Counting cells with formula that don't return blank

I want to count the number of non-"blank" cells in a column. I have a column of cells with a concatenate() for their corresponding row, i.e.:
=CONCATENATE(I10,J10,K10)
=CONCATENATE(I11,J11,...

1
vote

3
answers

498
views

### How can I nest a SUMPRODUCT by month within COUNTIFS for three conditions

How can I nest a SUMPRODUCT by month within COUNTIFS for three conditions. I need to count the number of items that are "Issued" in the status column that were issued within a specific year &...