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.
197
questions
0
votes
4
answers
159
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 ...
0
votes
1
answer
70
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.
...
0
votes
1
answer
124
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
vote
1
answer
35
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 | ...
0
votes
1
answer
237
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
650
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 ...
0
votes
2
answers
681
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 ...
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 ...
0
votes
3
answers
130
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 ...
0
votes
1
answer
64
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
vote
2
answers
772
views
Countif Date Array Formula
I'm trying to build a sheet for tracking compliance with training. I'm needing to build some summary fields, which show how many procedures a person is current on, how many they are out of date with, ...
0
votes
2
answers
4k
views
Count cells in a column between values until specific value reached in Excel
I am trying to count cells from a column between two values:
I have a list which on the range A2:A118. I want to start counting when a specific value is reached and stop when another specific value is ...
0
votes
1
answer
131
views
Finding Unique Values across many columns
I'm working on what I thought would be a simple task that has become quite complicated.
In Column A, I have a Field Name. In columns B through ANV (1059 columns) I have values associated with that ...
0
votes
2
answers
193
views
Count number of rows that have X in cell A or B or C
I think looking at the photo is the easiest way to show what I need:
Original Table
How do I set up a single formula to count the number of kids who failed an exam section?
What I've managed so ...
1
vote
2
answers
1k
views
Count how many times string is contained within string within a range in excel
I would like to parse through URLs and count how many times a string e.g. 'apple' occurs within a range. I tried =COUNTIF but it's looking for exact matches.
I have about 80k rows of URLs and I need ...
0
votes
1
answer
227
views
Count If Formula for 2 Separate Criteria
I am trying to write a CountIf Excel formula for 2 separate criteria on the same range. The range consists of a formula that generates a 1 or 2, so the Countif formula tells the user what category ...
0
votes
2
answers
149
views
Excel - keep the absolute reference from inserting row using COUNTIFS function
How can I use the absolute reference Column in a different sheet in COUNTIFS function?
COUNTIFS(Scenario!C:C,C21,Scenario!H:H,"PASS")
We are inserting new column on H, and then the reference range ...
1
vote
1
answer
166
views
Sum the Count of Min of each column by row
I am trying to automate a spreadsheet that will keep a running tally of the most negative topics week over week (kind of like Billboard top 100).
I have tried to get Column A(No.1) to calculate the ...
1
vote
2
answers
2k
views
COUNTIFS when criteria has more than 255 characters
I was looking for a formula in Excel that will search into different ranges parts of text that meet another criteria. So, basicalLy it is a COUNTIFS. While trying to use the formula, which perfectly ...
0
votes
2
answers
189
views
Count If: Using Formula to Create Range
I am trying to create the Range of a CountIf formula, using formulas. I found some data on using INDIRECT, but it doesn't seem to fit the bill. Here is the specific problem:
SHEET 1:
A B ...
0
votes
1
answer
302
views
Count number of rows matching multiple criteria
I'm working with two Excel workbooks. The first one contains this table:
The other contains this one:
Is it possible with VBA, for each week, to count the number of rows matching three specific ...
1
vote
1
answer
94
views
Cross table of joint games
I have a list of games of badminton that have been played over a period of time:
Each row contains the game id and one player id. Each game was played with four players. So, for each game there are ...
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
483
views
how to pass a string value into COUNTIF?
For this formula:
=COUNTIF(A1:A10,"jim")
how can I pass in a variable? That is, let's say that cell C11 has the value "jim"; can I then do something like:
=COUNTIF(A1:A10,TEXT_VALUE_OF_CELL(C11)) ...
1
vote
1
answer
1k
views
How to use Excel to calculate concurrent streaks
I want to use Excel to calculate the "best" and "current" streak in a range of data.
Say that a shop's sales target for the day is 500. I have a column which contains the daily sales data for the ...
0
votes
2
answers
572
views
Excel – using multiple criteria in COUNTIFS function
I need to create a formula that counts the number of times there is an overdue date in column I, where the corresponding cell in column J is blank.
I enter a date in column H (date appointed). There ...
0
votes
1
answer
54
views
How to properly use COUNTIFS with a defined named range?
I have a formula that counts the number of contact attempts for each month. The problem I am facing is that the formula only goes up to row 2000. How can I rewrite my formula to adjust with newly ...
1
vote
1
answer
2k
views
Trying to use the value of a cell for criteria in COUNTIF
I've constructed a gradebook in LibreOffice Calc, and I'm trying to keep track of how many students have scored an A, B, etc., on assignments. Currently, I have the grades for an assignment in a ...
-1
votes
1
answer
231
views
Using COUNTIFS function with criteria if a value is in range of values
I need help with COUNTIFS function.
There are values in column B: a,b,c,d,e,f,....
I want to use COUNTIFS function to count items in column A, if the values in column B is included in range {a,c,f}
0
votes
1
answer
317
views
How can I count the number of occurrences in a range of cells?
I am looking to count the number of times a value is found in a range of cells between two dates. My range is not a table but has headers for "Date", "Time" and "Result". It stretches from $G8:KT$992. ...
0
votes
1
answer
250
views
Add a CountIF to a SumProduct Formula
In a previous question, Scott gave me this great formula to sum all the sales from salespeople who saw an increase in sales from 2017 to 2018:
=SUMPRODUCT(C2:C9-B2:B9, --(C2:C9>B2:B9), --(B2:B9>...
2
votes
3
answers
7k
views
How to count cells if it contains a value in Excel
I have an Excel sheet like this:
ID | Relations
----+----------------
1 | ,
2 | ,
3 | ,1,
4 | ,1,2,
5 | ,2,
6 | ,3,
7 | ,1,2,4,
8 | ,1,2,4,5,6,
9 | ,2,4,5,1,
I want to count ...
0
votes
0
answers
223
views
Using COUNTIFS to count times within next 6 hours, next 12 hours, etc
I currently have COUNTIFS set up to count times in specific 6 hour time frames, for instance 12:00-18:00, but would like to change it to 6 hour increments FROM the CURRENT time: 6, 12, 18, and 24.
...
1
vote
1
answer
2k
views
Count multiple letters in different cells
I'm looking for a formula that can count different letters included in different cells.
Example:
Cell A2 includes a letter "S"
Cell A3 includes a letter "F"
Cell A4 includes a letter "N"
Cell A5 ...
1
vote
1
answer
44
views
Excel: Using COUNTIF but not including blanks with other formulas
Firstly, apologies if this is covered somewhere deep within the site, I have looked through a lot of other posts and none of the solutions have worked for me.
I'm creating a workbook for a local ...
1
vote
2
answers
469
views
Count appearances of two-adjacent-cell value in range (Excel)
I am working on a sports analytics tracker in Excel, and for one part I need to count (I know there are other questions that just need to find them) how many appearances there are of a two-cell value ...
0
votes
1
answer
290
views
Countif for Dates
I need a formula for excel that says to countif (G3 >= BK (which is a date) and < BL3 (which is another date.
Countif the cell is equal to or greater than a cell, but less than another date(trying ...
0
votes
1
answer
71
views
how to use Countifs function Excel?
from what i have been reading online it appears Countifs function is what i need to use for my problem. I have data that looks like this and goes on for several months:
|Month |Day| Time ...
0
votes
1
answer
268
views
Excel MOD function/countif logic work around the 12am point
My question is similar to this one in that I'm struggling with Excel's Mod formula. Mostly I just wish for clarification on how Mod function works in relation to time and if I'm making this harder ...
2
votes
2
answers
3k
views
How to Count Unique Dates for a Given Year in Excel
I have a list of dates in Column A of Excel as follows (header is cell A1):
Date
3/3/2015
3/10/2015
3/10/2015
4/15/2015
4/10/2016
4/29/2016
4/29/2016
5/5/2017
5/5/2017
6/7/2017
11/11/2017
2/3/2018
...
0
votes
1
answer
91
views
Count how many unique values are there in a column, While satisfying another columns requirement
Hello I have data in a table in the form:
Order# Origin Status Date
90028 BG00 30 1/2/2018
90021 BG01 50 2/2/...
0
votes
2
answers
82
views
Count specific items separately in 1 cell
I need a formula like COUNTIF for this problem.
Forexample:
I write ''a, b, c, d, d'' in 1 cell, but want them to be counted separately like this:
a -> 1
b -> 1
c -> 1
d -> 2
I tried with ...
1
vote
2
answers
3k
views
COUNTIF Formula not returning expected values
I'm currently attempting to create a formula for my spreadsheet that will calculate the total completion percentage of the form by counting all "Pass" and/or "Fail" results found within columns B ...
0
votes
1
answer
1k
views
IF=0, COUNTIFS, #NAME? error
I have a COUNTIFS function that is working perfectly, but if I add a IF=0 to the beginning of the function I get a #NAME? error. I have that technique working in other cells of the Excel file; I have ...
6
votes
3
answers
1k
views
Count unique value ignoring Text in range
123
562
My Data
548
K166
You
1548
123
562
100
My Data
100
I want to count unique values by ignoring text in the above shown list.
For this I've tried an array formula, {=SUM(1/Countif(,,,,))} but it'...
1
vote
4
answers
552
views
Counting multiple ranged criteria
this is my first post on this forum, so it's probably presented in a very bad way. I am using Excel 2007 to collect some data for my thesis. I have recently learnt how to nest COUNTIFS inside ...
1
vote
1
answer
63
views
how to save time with multiple COUNTIFS possibilities?
I am sure that there is a smarter approach then this brute one:
=IF(COUNTIFS(O40<>P40;O40<>Q40;O40<>R40;O40<>S40;O40<>T40;O40<>U40;
p40<>o40;p40&...
1
vote
1
answer
474
views
How to change Range in Countif formula based on list in Column A
So I have a worksheet that has 2 columns
column A(name)(Cell A1) - Column B(how many resets)(Cell B1)
Sheet1(Cell A2) YES(Cell B2)
Sheet2(Cell A3) YES(Cell B2)
Sheet3(Cell A4) YES(Cell B4)
...
0
votes
1
answer
51
views
Any way to use function results for a countif conditional in Excel [duplicate]
For example I have a list of dates in A2 through A31 (1st April to 30th April) and want to count the number of weekdays.
In B2 to B31 I could use =WEEKDAY(A2,2)<6 to give true or false. Then ...
0
votes
1
answer
77
views
Countif working with Excel to count only the first name in the list
I would like to use this formula to check column A with below names:
SUM(COUNTIFS([Consolidate.xls]RawData!$A:$A,{"*Kobe Bryant*","*John Legend*","*Tom Kitten*","*Jason Kidd*","*Michael Jordan*"}))
...