All Questions
100
questions
0
votes
3
answers
75
views
Functions based on multiple criteria
I want to do some count functions based on multiple criteria. I have a sample dataset here:
I want to find the number of Males if BD = MCD or FSGS.
For this I tried:
=COUNTIF(Gender,"M")+...
- 15
0
votes
1
answer
33
views
How to get total QTY from two columns, and get QTY based on location and date range
So I have been trying to get an answer to this situation and I have tried a slew of functions from countifs, sumifs and its not working as I had hoped.
What I am trying to do: I have two tabs in excel ...
1
vote
1
answer
24
views
How to get unique count of values within a specific date range avoiding duplicate values, for a specific location
I am attempting to obtain the sum of a column that has duplicate values, and have those duplicate values counted as 1 instance within a specific date range, that is representing the reporting week. So ...
1
vote
1
answer
141
views
How to get unique count of values within a specific date range avoiding duplicate values
I am attempting to obtain the sum of a column that has duplicate values, and have those duplicate values counted as 1 instance within a specific date range, that is representing the reporting week. So ...
0
votes
1
answer
122
views
Excel: Using COUNTIF + UNIQUE gives generic error "There's a prblem with this formula"
I'm trying to create an excel function that counts the number of unique entries in a range of (potentially merged) cells. The UNIQUE function returns two values for merged cell, one of which is zero. ...
- 1
0
votes
2
answers
86
views
Counting the number of times a value appears in a range of columns
Consider the following table which shows a generalised, abstract version of my specific real-world problem (which I will also illustrate).
Each "1" or "0" is part of a column ...
- 103
0
votes
3
answers
190
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 ...
- 445
0
votes
1
answer
128
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 ...
- 3
0
votes
2
answers
3k
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
-1
votes
1
answer
582
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 ...
4
votes
4
answers
599
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, "...
- 105
0
votes
1
answer
25
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
171
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
2k
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
0
votes
3
answers
1k
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 ...
1
vote
1
answer
161
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
2
votes
1
answer
62
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
881
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 ...
- 113
1
vote
1
answer
505
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
1
vote
1
answer
304
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 ...
- 119
1
vote
1
answer
512
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
0
votes
1
answer
41
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
2k
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
3
answers
7k
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
504
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
1
answer
221
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
941
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
0
votes
4
answers
379
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
75
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.
...
- 119
0
votes
1
answer
767
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
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
137
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
144
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
211
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
0
votes
1
answer
235
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
162
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
1
vote
1
answer
208
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 ...
- 11
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 ...
- 21
0
votes
2
answers
235
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 ...
- 111
1
vote
1
answer
100
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 ...
- 13
1
vote
2
answers
15k
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 ...
- 1,650
0
votes
1
answer
516
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,650
0
votes
2
answers
594
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 ...
- 3
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
-1
votes
1
answer
295
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}
- 2,985
0
votes
1
answer
349
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. ...
- 17
0
votes
1
answer
281
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>...
- 119
0
votes
0
answers
259
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 ...
- 15
1
vote
2
answers
517
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 ...
- 212