Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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
1answer
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
3answers
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
3answers
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
4answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
2answers
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
2answers
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
2answers
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
0answers
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
2answers
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
1answer
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
0answers
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
2answers
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
1answer
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
1answer
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
2answers
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
0answers
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
0answers
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
3answers
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
2answers
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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 ...