countif when 2 values in a cell
 

I'm using 1 column. It will contain A, B, C, D, etc. My countif works great
when I count A's, B's etc. But there are times there is an AB or A B in the
cell. It neither counts the A or the B. Any suggestions. I see some solutions
of countif were pivot tables. If that's my solution, I'll need beginner help.
Thanks to all.

countif when 2 values in a cell
 

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif works
great
when I count A's, B's etc. But there are times there is an AB or A B in
the
cell. It neither counts the A or the B. Any suggestions. I see some
solutions
of countif were pivot tables. If that's my solution, I'll need beginner
help.
Thanks to all.

countif when 2 values in a cell
 

Thank you! I used the wildcard - it reminded me of my days in DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif works
great
when I count A's, B's etc. But there are times there is an AB or A B in
the
cell. It neither counts the A or the B. Any suggestions. I see some
solutions
of countif were pivot tables. If that's my solution, I'll need beginner
help.
Thanks to all.

countif when 2 values in a cell
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Thank you! I used the wildcard - it reminded me of my days in DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif works
great
when I count A's, B's etc. But there are times there is an AB or A B in
the
cell. It neither counts the A or the B. Any suggestions. I see some
solutions
of countif were pivot tables. If that's my solution, I'll need beginner
help.
Thanks to all.

countif when 2 values in a cell
 

Hi. This post has helped me, but only to a point.

I am trying to calculate something along the lines of, "COUNT If there is a
":" in a cell in column A (Due to the cell being a time), and colum B is
greater than 89.9"

I used your idea to be able to count all the cells in Column A by using
=COUNTIF(A:A,"*:*") but I can't figure out the look-up part to count column B.

Any ideas?

Thank you in advance,
Greg

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Thank you! I used the wildcard - it reminded me of my days in DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif works
great
when I count A's, B's etc. But there are times there is an AB or A B in
the
cell. It neither counts the A or the B. Any suggestions. I see some
solutions
of countif were pivot tables. If that's my solution, I'll need beginner
help.
Thanks to all.

countif when 2 values in a cell
 

The problem is that the cell is a number (that's what dates and times are to
excel) and you see the colons because of the way the cell is formatted.

Change the format to General and you'll see that there's no colon in the value
of that cell.

So if all your times are less than Midnight (24 hour clock) and there are no
other numbers between 0 and 1 in that range:

=sumproduct(--(isnumber(a1:a10)),--(a1:a10=0),--(a1:a10<1))

0 is midnight of the day.
1 is midnight of the next day.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Greg wrote:

Hi. This post has helped me, but only to a point.

I am trying to calculate something along the lines of, "COUNT If there is a
":" in a cell in column A (Due to the cell being a time), and colum B is
greater than 89.9"

I used your idea to be able to count all the cells in Column A by using
=COUNTIF(A:A,"*:*") but I can't figure out the look-up part to count column B.

Any ideas?

Thank you in advance,
Greg

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Thank you! I used the wildcard - it reminded me of my days in DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif works
great
when I count A's, B's etc. But there are times there is an AB or A B in
the
cell. It neither counts the A or the B. Any suggestions. I see some
solutions
of countif were pivot tables. If that's my solution, I'll need beginner
help.
Thanks to all.







--

Dave Peterson

countif when 2 values in a cell
 

Thanks Dave.

However, I am still having problems. The data comes from a 3rd party, and
what I am trying to do is to say, if Column A is a Time, count Column B if
Column B is 89.9.

Here's some of my data (all formated as general per the data dump):

6/1/2009 6.7
08:15 0
08:30 50
08:45 100
10:00 40
10:15 92
6/2/2009 93
10:45 100
11:00 47
11:15 95

So, in this case the answer would be 4.

Thanks in advance,

Greg

"Dave Peterson" wrote:

The problem is that the cell is a number (that's what dates and times are to
excel) and you see the colons because of the way the cell is formatted.

Change the format to General and you'll see that there's no colon in the value
of that cell.

So if all your times are less than Midnight (24 hour clock) and there are no
other numbers between 0 and 1 in that range:

=sumproduct(--(isnumber(a1:a10)),--(a1:a10=0),--(a1:a10<1))

0 is midnight of the day.
1 is midnight of the next day.

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Greg wrote:

Hi. This post has helped me, but only to a point.

I am trying to calculate something along the lines of, "COUNT If there is a
":" in a cell in column A (Due to the cell being a time), and colum B is
greater than 89.9"

I used your idea to be able to count all the cells in Column A by using
=COUNTIF(A:A,"*:*") but I can't figure out the look-up part to count column B.

Any ideas?

Thank you in advance,
Greg

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Thank you! I used the wildcard - it reminded me of my days in DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif works
great
when I count A's, B's etc. But there are times there is an AB or A B in
the
cell. It neither counts the A or the B. Any suggestions. I see some
solutions
of countif were pivot tables. If that's my solution, I'll need beginner
help.
Thanks to all.







--

Dave Peterson

countif when 2 values in a cell
 

my data (all formated as general per the data dump):

So, I guess it's a possibility that your data is really TEXT even though it
looks like dates/times.

See if this works:

=SUMPRODUCT(--(ISNUMBER(FIND(":",A2:A11))),--(B2:B1189.9))


--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
Thanks Dave.

However, I am still having problems. The data comes from a 3rd party, and
what I am trying to do is to say, if Column A is a Time, count Column B if
Column B is 89.9.

Here's some of my data (all formated as general per the data dump):

6/1/2009 6.7
08:15 0
08:30 50
08:45 100
10:00 40
10:15 92
6/2/2009 93
10:45 100
11:00 47
11:15 95

So, in this case the answer would be 4.

Thanks in advance,

Greg

"Dave Peterson" wrote:

The problem is that the cell is a number (that's what dates and times are
to
excel) and you see the colons because of the way the cell is formatted.

Change the format to General and you'll see that there's no colon in the
value
of that cell.

So if all your times are less than Midnight (24 hour clock) and there are
no
other numbers between 0 and 1 in that range:

=sumproduct(--(isnumber(a1:a10)),--(a1:a10=0),--(a1:a10<1))

0 is midnight of the day.
1 is midnight of the next day.

Adjust the ranges to match--but you can't use whole columns (except in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Greg wrote:

Hi. This post has helped me, but only to a point.

I am trying to calculate something along the lines of, "COUNT If there
is a
":" in a cell in column A (Due to the cell being a time), and colum B
is
greater than 89.9"

I used your idea to be able to count all the cells in Column A by using
=COUNTIF(A:A,"*:*") but I can't figure out the look-up part to count
column B.

Any ideas?

Thank you in advance,
Greg

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Thank you! I used the wildcard - it reminded me of my days in DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif
works
great
when I count A's, B's etc. But there are times there is an AB or
A B in
the
cell. It neither counts the A or the B. Any suggestions. I see
some
solutions
of countif were pivot tables. If that's my solution, I'll need
beginner
help.
Thanks to all.







--

Dave Peterson

countif when 2 values in a cell
 

Worked perfectly! Hooray!

Thank you very much.

Greg

"T. Valko" wrote:

my data (all formated as general per the data dump):

So, I guess it's a possibility that your data is really TEXT even though it
looks like dates/times.

See if this works:

=SUMPRODUCT(--(ISNUMBER(FIND(":",A2:A11))),--(B2:B1189.9))


--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
Thanks Dave.

However, I am still having problems. The data comes from a 3rd party, and
what I am trying to do is to say, if Column A is a Time, count Column B if
Column B is 89.9.

Here's some of my data (all formated as general per the data dump):

6/1/2009 6.7
08:15 0
08:30 50
08:45 100
10:00 40
10:15 92
6/2/2009 93
10:45 100
11:00 47
11:15 95

So, in this case the answer would be 4.

Thanks in advance,

Greg

"Dave Peterson" wrote:

The problem is that the cell is a number (that's what dates and times are
to
excel) and you see the colons because of the way the cell is formatted.

Change the format to General and you'll see that there's no colon in the
value
of that cell.

So if all your times are less than Midnight (24 hour clock) and there are
no
other numbers between 0 and 1 in that range:

=sumproduct(--(isnumber(a1:a10)),--(a1:a10=0),--(a1:a10<1))

0 is midnight of the day.
1 is midnight of the next day.

Adjust the ranges to match--but you can't use whole columns (except in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Greg wrote:

Hi. This post has helped me, but only to a point.

I am trying to calculate something along the lines of, "COUNT If there
is a
":" in a cell in column A (Due to the cell being a time), and colum B
is
greater than 89.9"

I used your idea to be able to count all the cells in Column A by using
=COUNTIF(A:A,"*:*") but I can't figure out the look-up part to count
column B.

Any ideas?

Thank you in advance,
Greg

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Thank you! I used the wildcard - it reminded me of my days in DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My countif
works
great
when I count A's, B's etc. But there are times there is an AB or
A B in
the
cell. It neither counts the A or the B. Any suggestions. I see
some
solutions
of countif were pivot tables. If that's my solution, I'll need
beginner
help.
Thanks to all.







--

Dave Peterson

countif when 2 values in a cell
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
Worked perfectly! Hooray!

Thank you very much.

Greg

"T. Valko" wrote:

my data (all formated as general per the data dump):

So, I guess it's a possibility that your data is really TEXT even though
it
looks like dates/times.

See if this works:

=SUMPRODUCT(--(ISNUMBER(FIND(":",A2:A11))),--(B2:B1189.9))


--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
Thanks Dave.

However, I am still having problems. The data comes from a 3rd party,
and
what I am trying to do is to say, if Column A is a Time, count Column B
if
Column B is 89.9.

Here's some of my data (all formated as general per the data dump):

6/1/2009 6.7
08:15 0
08:30 50
08:45 100
10:00 40
10:15 92
6/2/2009 93
10:45 100
11:00 47
11:15 95

So, in this case the answer would be 4.

Thanks in advance,

Greg

"Dave Peterson" wrote:

The problem is that the cell is a number (that's what dates and times
are
to
excel) and you see the colons because of the way the cell is
formatted.

Change the format to General and you'll see that there's no colon in
the
value
of that cell.

So if all your times are less than Midnight (24 hour clock) and there
are
no
other numbers between 0 and 1 in that range:

=sumproduct(--(isnumber(a1:a10)),--(a1:a10=0),--(a1:a10<1))

0 is midnight of the day.
1 is midnight of the next day.

Adjust the ranges to match--but you can't use whole columns (except in
xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues
and
falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html


Greg wrote:

Hi. This post has helped me, but only to a point.

I am trying to calculate something along the lines of, "COUNT If
there
is a
":" in a cell in column A (Due to the cell being a time), and colum
B
is
greater than 89.9"

I used your idea to be able to count all the cells in Column A by
using
=COUNTIF(A:A,"*:*") but I can't figure out the look-up part to count
column B.

Any ideas?

Thank you in advance,
Greg

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
Thank you! I used the wildcard - it reminded me of my days in
DOS!

"T. Valko" wrote:

**Maybe** something like this:

=COUNTIF(A1:A10,"*A*")

That will count any cell that contains an A anywhere in the
cell.

If you use a cell reference:

B1 = A

=COUNTIF(A1:A10,"*"&B1&"*")

--
Biff
Microsoft Excel MVP


"terryc" wrote in message
...
I'm using 1 column. It will contain A, B, C, D, etc. My
countif
works
great
when I count A's, B's etc. But there are times there is an AB
or
A B in
the
cell. It neither counts the A or the B. Any suggestions. I
see
some
solutions
of countif were pivot tables. If that's my solution, I'll
need
beginner
help.
Thanks to all.







--

Dave Peterson