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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
54 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
15 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
66 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. ...
1 vote
2 answers
449 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) ...
0 votes
2 answers
4k views

Excel 2010 Conditional Formatting Accross multiple tables

I have two tables with values in columns A & B. For both tables A is a numeric value and B is text value. Example below. I want to highlight cells in Table 2-Column B for which the cell in ...
1 vote
1 answer
3k views

using functions in COUNTIF criteria

Do you know 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 ...
0 votes
2 answers
73 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
97 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
47 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
48 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
74 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
1 answer
18 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,...
0 votes
2 answers
2k 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 ...
1 vote
1 answer
35 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
52 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 votes
1 answer
436 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
1k 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
5 answers
58k 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 ...
0 votes
1 answer
202 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
579 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
463 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
152 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
815 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
891 views

count unique values that start with specific criteria

from Data column like to see how many unique values i have starting with CC (=3) AB0010 CC0009 AB0010 EF0011 CC1001 AB0090 CC1001 (empty field) CC1001 CC2001 Start with: AB = 2 CC = 3 EF = 1
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
26 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
893 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
267 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
135 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
356 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 ...
0 votes
1 answer
532 views

How to exclude strings that end in any number when using COUNTIF?

I have a table and I want to count the number of times a string appears on a column. I used COUNTIF to do this, but I want to exclude in the count those cells with strings ending in any number. I ...
2 votes
1 answer
60 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
723 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
69 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 ...
2 votes
4 answers
217 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
442 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). ...
1 vote
1 answer
272 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
170 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
418 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
28 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 ...
1 vote
2 answers
14k views

Simple pattern with Excel COUNTIF to match a substring?

How do I fix this formula: =COUNTIF(A1:A10,"*jim*") to match anything containing the sub-string of "jim" but from a cell, as: =COUNTIF(A1:A10,C11) where cell C11 contains the text "jim"? Perhaps ...
0 votes
1 answer
38 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
104 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
463 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
2 answers
567 views

Count first cells in a range of rows, which are higher than 0 and meet criteria

For each column, I would like to count the amount of cells, which are higher than 0, appear for the first time in the row, and meet a criteria in one of the columns. In this example, I would like to ...