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.
187
questions
0
votes
2
answers
109
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 ...
- 3
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,...
1
vote
1
answer
20
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
0
votes
1
answer
15
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
2
answers
95
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
-1
votes
1
answer
95
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
91
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
0
votes
1
answer
125
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 ...
- 1
4
votes
4
answers
559
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, "...
- 95
0
votes
1
answer
262
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 ...
- 331
0
votes
1
answer
24
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
0
votes
2
answers
79
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
267
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
296
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
-1
votes
1
answer
21
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
0
votes
3
answers
217
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
136
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
1
vote
1
answer
53
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
0
votes
1
answer
186
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 ...
- 1
2
votes
1
answer
53
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
1
answer
345
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
59
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
291
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).
...
- 139
2
votes
4
answers
159
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
1
answer
189
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 ...
- 109
0
votes
3
answers
107
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
1
answer
222
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
0
answers
27
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
31
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])
- 11
0
votes
1
answer
1k
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 (...
- 1
0
votes
1
answer
83
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
0
votes
3
answers
5k
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
2
answers
278
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
0
votes
3
answers
2k
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,...
- 123
1
vote
3
answers
379
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 &...
- 11
0
votes
1
answer
208
views
How to use a COUNTIF while simultaneously grouping duplicates?
So I have the following table
I'm trying to create a summary in another tab that counts the number of codes by Phase as well as the number of Platforms. For the number of codes, I'm able to use a ...
- 13
0
votes
2
answers
399
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 ...
- 1
0
votes
1
answer
143
views
Inputting several values, found from a VLOOKUP function, into a COUNTIF function
This question is about a single function (in a single cell to later extrapolate over a whole table) in Microsoft Excel.
I want to find a unique id on another page (amongst other id's in the first ...
- 103
1
vote
3
answers
483
views
Counting unique values against a subset of a list
I'm trying to count how many unique supervisors a particulate employee has. (See an example data set below.)
I'm able to do this in a round about way, but I'm wondering if any of you experts out ...
- 113
1
vote
1
answer
256
views
SUMIF versus SUMIFS
SUMIF allows to sum using a single criteria, where SUMIFS works with multiple criteria.
I tend to find that on many occasions, I've start with SUMIF and then need to add more criteria, so change the ...
- 405
0
votes
4
answers
60
views
Need To Count Total Number Of Instances Of Values In A Column And Return The Results In Other Cells
I have a situation where I need to count the total number of instances of values in a column and return the results in other cells.
Users are going to be provided this Excel sheet as a template and ...
- 3
0
votes
1
answer
52
views
How to combine multiple COUNTIF in Excel?
I have this formula:
=OtherSheet!$B$2*COUNTIF(C6:CX6;OtherSheet!$A$2)
It looks in the columns to the right and for each occurrence of a string from the other sheet's A2 it adds the other sheet's B2.
...
- 109
0
votes
1
answer
86
views
Why does my Excel COUNTIF function always return a value of “0”?
My HTML is:
div class="medium-insert-images" data-media-id="47deed170ef76561" data-original-width="590" data-original-height="350" contenteditable="false"figure img src="https://cdn-blog.scorum....
- 1
1
vote
1
answer
28
views
Multiple values same line COUNTIF
I have a table as follows for a musical instrument casting sheet [titled Song Casting)
Here's an MWE:
+--------+------+--------+-------+------+--------+---------+
| Song | Bass | Guitar | Drums | ...
- 111
0
votes
1
answer
158
views
Counting the occurence of active durations by month and into a graph
Lets assume I currently have a spreadsheet that holds the dates of members at a crossfit gym and the duration of their memberships.
Date Started Date Ended Member Name
11/27/2019 12/19/2019 ...
0
votes
1
answer
408
views
Excel "IFS" criteria values using cell references
My data consists of a month column A (1 through 12) and a values column B (financial returns) spanning multiple years. How do I count the number of month occurrences in the second half of the year ...
- 1
0
votes
2
answers
535
views
How to COUNTIFS with OR and >= Operator logic
I've found threads that addressed OR logic or operator logic but none together and it's not working for me.
Let's say I have a table like this:
Col1 Col2(Date) Col3(Date)
I want to use ...
- 1
0
votes
1
answer
1k
views
How to count distinct values in one column for each distinct value in another column in MS Excel?
Say I have the following data in an Excel sheet:
Country Fruit
USA Banana
USA Apple
USA Apple
USA Orange
USA Banana
USA Apple
UK Banana
UK Banana
UK Apple
I ...
- 105
0
votes
3
answers
103
views
Excel - Indirect Formula
another question regarding Excel Indirect Formula.
I have columns E1:J202 filled with one of the following:
Green
Amber
Red
How can I use the indirect formula to count each of the above separately ...
- 21
0
votes
1
answer
40
views
Count of number of times a max of the dates in two columns is bigger than the date in the third column
I have three columns in a report: a closure date (Col A), an implementation date (Col B) and a target date (Col C).
I need to count the number of times an entry is closed or implemented before the ...
- 1