Biff,

I figured. Have you ever been in the teaching profession? Good teachers are hard to find.

I have discovered something interesting with LOOKUP( ) and have started a thread.

On the subject of teaching "focus," have you read the following before?

************************************************** ******

Mary's father has five daughters: 1. Nana, 2. Nene, 3. Nini, 4.
Nono.
What is the name of the fifth daughter?

Scroll down.










Answer: Nunu? NO! Of course not. Her name is Mary.
************************************************** ******
Epinn

"Biff" wrote in message ...
It doesn't make any difference if we use 1 or 100 when we use COUNT( ).
But I think 100 can easily trick us (at least me) into thinking SUM ( )
even when COUNT( ) is staring at us.

That was my intention in using 100.

Biff

"Epinn" wrote in message
...
Anyone wants to hear a joke? It's on me. I was stuck when I tried to come
up with a formula using COUNT( ). Why? I thought I had to include an array
in the formula for COUNT( ) to count and I couldn't figure out ...... I
forgot that *values* are okay too. I tried to reference some COUNT( )
examples. Believe it or not, all of them have an array. There are
scenarios that count without using an array but they use SUM( ) and the
value "1." As a result, I am convinced that I need an array and I am more
stuck. Today, I finally find an example of COUNT( ) counting value. Guess
what, it is not purely counting value; it still counts an array *and* a
value.

Why are users more *inclined* to use SUM and 1 instead of COUNT and 1? Is
there some kind of a "secret" that I am not aware of? :)

Biff, you are such a good teacher; you don't spoon-feed. Unfortunately, in
this case, it's not so much about me not "thinking," it is a case of me not
"remembering correctly" *and* am convinced by the examples that my theory is
correct.

You brought up a very good point. It doesn't make any difference if we use
1 or 100 when we use COUNT( ). But I think 100 can easily trick us (at
least me) into thinking SUM ( ) even when COUNT( ) is staring at us. I have
no problem with the Boolean though.

This thread has helped me see COUNT, SUM and SUMPRODUCT as one big picture.
I hope I can easily jump from one function to another in the future. I
can't do it without the help from the group. Appreciated.

Epinn

"Biff" wrote in message
...
Try this and see if you can figure out why it works. (just trying to "push"
you to think)

(don't use Evaluate Formula until you've given up)

array entered

=COUNT(IF((A1:A10="A")*(E1:E10="J"),100))

Biff

"Epinn" wrote in message
...
The following formulae yield the same results.



=SUMPRODUCT((A1:A10="a")*(E1:E10="j"))

=SUM((A1:A10="a")*(E1:E10="J")) array formula

=SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula

=SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula



I realize that SUM(IF(AND won't work because AND ( ) causes the entire array
to be treated as one element.



I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the
following.



Column H: =AND(A1="a",E1="j") (copied down the column). Then use the
following formula:

=COUNTIF(H1:H10,TRUE)



Is it possible to use COUNT without a helper column?



I am trying to learn here. Thank you.



Epinn