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

**0**

votes

**1**answer

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

**1**

vote

**3**answers

52 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

29 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

138 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

28 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

62 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

81 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

**0**answers

38 views

### Complex if then arguments in Excel

I have a calendar of months with time off codes on certain days an employee took off and has scheduled off. Codes are "V" for vacation, "PTO" for Paid Time Off, etc.
I am trying to input a formula ...

**0**

votes

**1**answer

29 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

20 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

341 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

52 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

57 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

241 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

47 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

345 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

135 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

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

**0**

votes

**1**answer

59 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

31 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

41 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*"}))
...

**0**

votes

**0**answers

168 views

### Excel COUNTIF gives different result for two different ranges that contain the same matching cell

This is one of the strangest behaviours I have ever seen for Excel...
In cell A3 I have the formula =COUNTIFS(A$101:A$109,"=ERR!"), cells A101 through A109 contain {,"-",,"-","-",,"ERR!",}. This ...

**1**

vote

**2**answers

62 views

### Counting Non-repeat-valued Cells in Excel

I have a column of (text) values. I want to count the number of times a non-blank cell arises such that its value differs from that of the cell immediately above it. I do not want just a sheer count ...

**0**

votes

**1**answer

27 views

### COUNTIFS with dates

I'm trying to get the number of support tickets for each month. In my first tab "Tickets" I have:
The date values have the following characteristics:
In my second tab I have:
I want to know how ...

**0**

votes

**1**answer

21 views

### Count values depending on Equality Compare

I have a tables that looks like the following:
I have multiple Item rows, and for each there are many Value columns, and a single Result. What I would like to do in my TOTAL is, for each Value column,...

**0**

votes

**1**answer

59 views

### Excel: Data across multiple columns and rows

Currently have a table like this:
How can I consolidate and count the hours per person spent on each day. Need to be on seperate lines due to working on different tasks but want to bring in all ...

**2**

votes

**2**answers

687 views

### Display the lines that meet the COUNTIF criteria

Not sure if this is possible but when using the COUNTIF function is it possible to actually find the lines that meet the criteria.
For example I have a COUNTIF formula that looks at a large amount of ...

**0**

votes

**1**answer

49 views

### MOS Test MS Excel 2013 Formula COUNTIF

Today I was giving MOS Test for Excel 2013 and I gone through a formula COUNTIF. But the range of formula was specified in very strange way and I did not understand how to select the range. I am ...

**2**

votes

**2**answers

421 views

### Advanced sumproduct

I'm trying to condense a column of formulas into an advanced sum product which would effectively use a CountIf based on a criteria—but I'm having trouble putting the formula together.
How do you ...

**0**

votes

**2**answers

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

**2**

votes

**2**answers

827 views

### CTRL+SHFT+ENTER Array Formula not working

So my issue is a more complex one, but I have tried with a much simpler formula (with a similar case) and I get the same problem. The array formula I'm using only returns the results for the first ...

**0**

votes

**2**answers

87 views

### Wildcard in Excel is selecting full string instead of partial text

I'm having issues with using a wildcard in Excel.
In a column I want to find a value which is called "FFI".
Within this column there is also a number of values called "Office".
I've used two ...

**0**

votes

**0**answers

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

**1**

vote

**2**answers

42 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

191 views

### Excel countif not working as expected

My forumla is as follows =COUNTIFS(H4:H700,"Jacob",H4:H700,"Tobi",H4:H700,"refresh")
within the range of H4:h700, I have various different text strings. I only want to count when the text strings ...

**0**

votes

**0**answers

350 views

### Crate dynamic sub-tables using IF, COUNTIF, INDEX, SMALL

I am very much struggling with creating dynamic sub-tables from a master.
I want to drive through the content of each row (Rows: A-N) to seven further worksheets determined by the "Practice" that ...

**0**

votes

**2**answers

838 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

250 views

### CountIf Comparing Dates

My CountIf function is straight just not working, and I'm curious what I've done wrong. It only seems to output 1 or 0, and bizarrely is doing so differently depending on where in the sheet I put the ...

**1**

vote

**1**answer

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

**0**

votes

**2**answers

157 views

### Countif with one Criteria, but only count Cost Center once

I have the following problem:
I have a list with 2 columns, in the first columns there are several cost centers and in the second are Values. I am looking formula, that counts every Cost Center if ...

**0**

votes

**0**answers

75 views

### Counting the Number of times a text appears in column based on condition of two or more columns

I have 2 columns in my worksheet. Containing the Month and status.
I want to count the number of times Each status appears for particular month and it should provide the result dynamically whenever ...

**0**

votes

**0**answers

48 views

### Excel: defining COUNTIF range based on values in a third column

I'm trying to sort out dyadic data using Excel. I have information on wars, sort of like this:
Conflict | Country | Government | Side | repdyads | mondyads |
Spanish-American War | ...

**0**

votes

**3**answers

2k 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

1k 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:

**1**

vote

**1**answer

411 views

### Excel Query date range for Value

Trying to query an excel document to determine the number of times a value appears so I can put in conditional formatting on and heat map to determine the number of times the resource is busy during a ...

**0**

votes

**1**answer

116 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

**0**answers

244 views

### Dynamic dropdown in Excel

Trying to created a searchable dynamic dropdown containing a list of all of our products for order processing at work.
It is searchable which is one of the main uses, but it only works for one of the ...

**0**

votes

**1**answer

161 views

### How to Count the Items that Occur a Certain Number of Times in a List

I am trying to simplify a task that requires counting of values that occur 3 or more times in a list and then simply making a list of these values in a column as can be seen in the image. I would be ...

**0**

votes

**1**answer

25 views

### Countif or sumif formula

I have a guest list for the event and I need to do statistics on RSVP.
| Column 1 | Column 2 | Column 3 |
| (name) | (if the guest is VIP, | (Count RSVP: |
...

**-1**

votes

**1**answer

108 views

### Attendance - Count total number of days each person showed up [closed]

Please see the table (spreadsheet) below. I'm trying to create a formula that will help me determine how many total days each person actually entered the building. You will see within the table some ...