countif when 2 values in a cell

[terryc]

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.

 

[T. Valko]

**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&"*")

 

[terryc]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Greg]

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

 

[Dave Peterson]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[Greg]

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

 

[T. Valko]

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:B11>89.9))

 

[Greg]

Worked perfectly! Hooray!

Thank you very much.

Greg

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Worked perfectly! Hooray!

Thank you very much.

Greg