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.
198
questions
1
vote
1
answer
2k
views
Excel- add sequential numbers to column but skip if duplicate data in matching column
2 questions: I have approx. 200 locations with equipment listing at each site. I have gateway#, name, type, location name. Most pieces of equipment are at unique locations; however, some of my ...
1
vote
1
answer
130
views
COUNTIF / Matching and determining repeating data
I have question for EXCEL. I am looking to determine how many times each number repeats with another number in a data set.
If I have a data set:
Line 1 - 1 2 5
Line 2 - 1 2 10
Line 3 - 1 6 9 10
...
1
vote
2
answers
455
views
How to use OR in a COUNTIF function when dealing with dates and ">" characters
I want to write a function whereby I could count a cell if it is blank OR greater than a date.
This is a piece in what will be a much larger formula so looking to avoid a solution of SUM(countif(D:D,...
1
vote
2
answers
2k
views
COUNTIF and SUM in Excel with different ranges
Is there a more efficient way of making the following function?:
>=COUNTIF(A1,">0")+COUNTIF(C1,">0")+COUNTIF(E1,">0")+COUNTIF(G1,">0")
I want to count if the cell is bigger than 0 and ...
1
vote
1
answer
1k
views
Excel Sort and Lookup Values Formula
I am trying to create some sort formulas on excel.
An example is shown below regarding players and their corresponding scores. I need to query the "Raw Data" and get the "Top 3" players and their ...
1
vote
1
answer
1k
views
Excel: How to count cells, whose date values represent a day between monday and friday?
I want to count cells, whose values are dates, under the following condition: The date value entered must be a day between Monday and Friday.
Assume the following data and my try:
Column A
--
15.01....
1
vote
1
answer
138
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 ...
1
vote
1
answer
732
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 ...
1
vote
1
answer
444
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
427
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
467
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 ...
1
vote
1
answer
335
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 ...
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 | ...
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 ...
1
vote
1
answer
169
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 ...
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
4
answers
561
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
476
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)
...
1
vote
2
answers
52
views
Excel count of number of times a unique value has been selected
I have a long file with three columns, the first column contains the name of institutions, the second column contains the name of customers within each institutions and the third columns contains a ...
1
vote
1
answer
66
views
Excel: Count the number of matches between lists
I have two lists, each on separate tabs. One list occupies B5:B10 (though the number of rows could change) and the other is named "proteins". I want to count the number of matches between the lists (...
1
vote
0
answers
1k
views
excel - how to count number of names in a column, name is specified in a cell
I have two worksheets, "Clients 2014" and "Receipts 2014".
The receipts have client names for each row (each row is a receipt).
The search key is in the "Clients 2014" worksheet.
I want to ...
1
vote
1
answer
754
views
Advanced data consolidation in Excel
I need to Combine/Consolidate Data dynamically between over 20 separate Excel workbooks. The Column names and their relative position at the top row are the same. The positioning of each word under ...
1
vote
1
answer
1k
views
How to count unique numbers based on month?
Column A include a list of numbers, some are duplicated
Column B include a list of numbers from 1-12 that represent the months
I need to count the unique numbers in column A where the month in ...
1
vote
2
answers
1k
views
In Excel, checking if a cell matches one item from a named constant string list
This question is an expansion on In Excel, how do I check if a cell is in a list of values (a range of cells) .
I'm trying to do =COUNTIF(name,G6) and when name refers to some cells, it works fine, ...
1
vote
0
answers
141
views
Custom function COUNTIFUNIQUES in Excel [duplicate]
Possible Duplicate:
Excel: how to count number of distinct values in a range?
I would like to create a custom function COUNTIFUNIQUES that extends the functionality of COUNTIFS.
Here is an ...
0
votes
9
answers
19k
views
Excel COUNTIF Slow
I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF ...
0
votes
1
answer
486
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)) ...
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 ...
0
votes
4
answers
666
views
Countifs function: two criteria in the same range for text
I am using the countif function to count only those items achieving a certain criteria. My problem is I have a certain range wherein there are two items (criteria) I want to include in my logic so ...
0
votes
3
answers
174
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 ...
0
votes
2
answers
775
views
Excel Matrix Condition - Is there a faster way?
I'd like to get some data depending on multiple entries.
I'm using the "NB.SI.ENS()" which is "COUNTIFS()" in English if I'm not mistaken.
I have multiple entries, which would represent a 3D array ...
0
votes
2
answers
7k
views
Count number of instances in Excel based on another cell
I want to count the number of times a name appears with "Yes" in the next column.
Table sample:
0
votes
3
answers
102
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
52
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
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 ...
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,...
0
votes
1
answer
208
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 ...
0
votes
2
answers
83
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 ...
0
votes
3
answers
11k
views
How do I assign values to cells?
I'm setting up a golf tournament and need help assigning dollar winnings to cells and then when I put someone's name in those cells it will auto calculate a running tally for me for each player for ...
0
votes
2
answers
45
views
Nested countif's frying my brain
What I want is the following scenario:
If all values are "Pass" then display "Passed"
If all values are "Fail" then display "Failed"
If all values are "Incomplete" then display "Not Started"
If all ...
0
votes
2
answers
266
views
Excel counting "A"'s with cell values of "A,B" "R,A" etc
Trying to count the number of individual variable in Excel but Countif doesn't seem to fit my needs
For example individual cells have "A","A,B", "A,J", etc and I want to count the number of "A"s - is ...
0
votes
5
answers
59k
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
2
answers
2k
views
Excel countifs criteria neither this nor that
Im trying to make a criteria for COUNTIFS such that the value of the cell is neither "pass" nor "fail" nor blank. How can I do this? I dont think "<>pass<>fail<>*" works
Since someone wanted ...
0
votes
1
answer
1k
views
How can I run SUMIF or COUNTIF with two Conditions?
I recently got some great help with SUMIF and COUNTIF and ended up with the following, working formulas;
=SUMIF(REC!$F:$F,$C2,REC!$G:$G)
=COUNTIF(REC!$F:$F,$C2)
I had been mixing up the syntax and ...
0
votes
1
answer
51
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
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
76
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 ...