Counting cells that contain a known value

  • Thread starter Thread starter gorkyness
  • Start date Start date
G

gorkyness

Two questions:

What formula would count the number of cells which contain some known
info. For example, trying to count how many cells contains the letter
"a" such as "apple" but not "orange".

What formula would count the number of mm/dd/yyyy dates in a particular
year?
 
Gorkyness,
What formula would count the number of cells which contain some known
info. For example, trying to count how many cells contains the letter
"a" such as "apple" but not "orange".
Click to expand...

Say you had the following in A1:A5
apple
orange
any
oompa
but

in B1 you put the letter (I assume you meant count the words starting with
a certain letter)

in C1
=SUMPRODUCT(--(LEFT(A1:A5,1)=B1))
What formula would count the number of mm/dd/yyyy dates in a particular
year?
Click to expand...

The number of dates would be the same as the number of days...

In A1:
January 1 for the year in question (must be a valid date)
In A2
January 1 for the following year (must be a valid date)

in B1:
=DATEDIF(A1, A2, "D")

More info on DATEDIF
http://www.cpearson.com/excel/datedif.htm
 
Dan said:
*Say you had the following in A1:A5
apple
orange
any
oompa
but

in B1 you put the letter (I assume you meant count the words starting
with
a certain letter)

in C1
=SUMPRODUCT(--(LEFT(A1:A5,1)=B1))
*
Click to expand...

Whoops, my bad - didn't notice "orange" had an "a" in it, lol. :D In
your example table...

apple
orange
any
oompa
but

...I want it to return a value of "4", because four words have an "a"
in them.

Dan said:
*The number of dates would be the same as the number of days...

In A1:
January 1 for the year in question (must be a valid date)
In A2
January 1 for the following year (must be a valid date)

in B1:
=DATEDIF(A1, A2, "D")

More info on DATEDIF
http://www.cpearson.com/excel/datedif.htm*Given the following example...
Click to expand...

7/4/03
10/29/97
12/16/03
2/13/95
10/31/99

...I want it to return a value of "2", because two dates are in 2003.
Click to expand...
 
Whoops, my bad - didn't notice "orange" had an "a" in it, lol. :D In
your example table...

apple
orange
any
oompa
but

..I want it to return a value of "4", because four words have an "a"
in them.
Click to expand...

=SUM((IF(ISERR(FIND(B1,A1:A5)),0,1)))
array entered (control + shift + enter)
if done correctly the whole formula will be bracketed with {}

Just in case you wanted to count all of the "a"'s
=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"a",""))))
7/4/03
10/29/97
12/16/03
2/13/95
10/31/99

..I want it to return a value of "2", because two dates are in 2003.
Click to expand...

=SUMPRODUCT(--(YEAR(A1:A5)=B5))
where B5 contains the year to search (ie. 2003)
 
=SUM((IF(ISERR(FIND(B1,A1:A5)),0,1)))
array entered (control + shift + enter)
if done correctly the whole formula will be bracketed with {}
Click to expand...

Easier non-array formula
=SUMPRODUCT((LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"a","",1))))
 
Back
Top