All Questions
100
questions
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'...
- 9,274
5
votes
2
answers
8k
views
Excel: Counting number of MAX values in a range
I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet:
A B C D
1 Round | Rita | Sue | Bob
------+------+-----+-----
2 1 | 5 | 3 | 4
3 ...
- 16.7k
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
3
votes
3
answers
15k
views
Include an OR condition in a COUNTIF?
This question has been asked before and the answer always seems to be to add together two COUNTIFs. This is fine for most applications, but won't work for me.
I have a column that can contain 0, 1 or ...
- 533
3
votes
1
answer
902
views
=countif says "0001234" is not equal to "0001234" How to force countif to treat strings as text not numbers?
I have some product codes, some of which are numbers with leading zeroes, stored in a spreadsheet.
I'm doing some operations that rely on a COUNTIF not equal to function (details in comments but not ...
- 774
2
votes
2
answers
619
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 ...
- 13.4k
2
votes
1
answer
1k
views
How to count the number of cells in a range containing a specific (sub)string with Excel/Calc?
Using Excel/Calc I have the following situation:
a range of cells containing values; assume it's A1:B10,
and a string I want to look for; for example, assume it is contained in C1.
Now what I want ...
- 1,996
2
votes
2
answers
4k
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
...
- 156
2
votes
1
answer
3k
views
How to use a an address formula as the limits of the COUNTIF range
E.g.
=COUNTIF(M13:O24,"S3")
I want M13 to instead be the value $W$13 which is in a cell given by
=ADDRESS(1+12,11+MATCH(AG12, Plan!$L$12:$Z$12, 0),1,)
I want O24 to instead be the value $W$51 ...
- 219
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
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
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
1
vote
1
answer
824
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 ...
- 581
1
vote
1
answer
1k
views
How does this SUMPRODUCT(COUNTIF( )) function work?
I was searching for a solution to count distinct values in a range, and came across this article. The example provides a list of ten names comprised of four unique entries. It then uses the ...
- 313
1
vote
1
answer
2k
views
Count matches between two arrays
How can I count the number of entries two arrays have in common, using just standard excel formulae (i.e. no VBA)?
Can't find an answer anywhere.
e.g.
A B
+-------------
1| 1 10
2| ...
- 141