Count cells
 

I have a long table which looks something like this....

1 4 7 A
1 6 5 D
2 7 4 C
4 8 2 D
3 2 9 A
2 1 4 F
5 6 7 D
1 2 3 A

I need a query which counts the number of times "A" appears in the 4th
column for every "1" in column 1. In this case the answer would be 2.

Count cells
 

Try this:

=SUMPRODUCT(--(A1:A10&D1:D10="1A"))

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"S Muzumdar" wrote in message
...
I
have a long table which looks something like this....

1 4 7 A
1 6 5 D
2 7 4 C
4 8 2 D
3 2 9 A
2 1 4 F
5 6 7 D
1 2 3 A

I need a query which counts the number of times "A" appears in the 4th
column for every "1" in column 1. In this case the answer would be 2.

Count cells
 

Works like a charm.... Thanks.

Quick question - what does the "--" between two parantheses do ?

Count cells
 

In this formula: =SUMPRODUCT(--(A1:A10&D1:D10="1A"))

this section: (A1:A10&D1:D10="1A")
returns a series of boolean (fancy word for: TRUE/FALSE) values
....which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to a boolean value,
Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.
It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

Now, you could easily use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, In the formula, the TRUE/FALSE values are converted to 1's and 0's
by the "--" and the SUMPRODUCT calculates the total.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"S Muzumdar" wrote in message
...
Works like a charm.... Thanks.

Quick question - what does the "--" between two parantheses do ?

Count cells
 

=SUM(IF(A1:A8=1,(IF(D1:D8="A",1,0))))

"S Muzumdar" wrote:

I have a long table which looks something like this....

1 4 7 A
1 6 5 D
2 7 4 C
4 8 2 D
3 2 9 A
2 1 4 F
5 6 7 D
1 2 3 A

I need a query which counts the number of times "A" appears in the 4th
column for every "1" in column 1. In this case the answer would be 2.

Count cells
 

I'd use:

=sumproduct(--(a1:a10=1),--(d1:d10="A"))

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

S Muzumdar wrote:

I have a long table which looks something like this....

1 4 7 A
1 6 5 D
2 7 4 C
4 8 2 D
3 2 9 A
2 1 4 F
5 6 7 D
1 2 3 A

I need a query which counts the number of times "A" appears in the 4th
column for every "1" in column 1. In this case the answer would be 2.

--

Dave Peterson

Count cells
 

Yes, that helps. Thanks a lot.

"Ron Coderre" wrote:

In this formula: =SUMPRODUCT(--(A1:A10&D1:D10="1A"))

this section: (A1:A10&D1:D10="1A")
returns a series of boolean (fancy word for: TRUE/FALSE) values
....which are not numeric to Excel.

When an arithmetic operator (+,-,*,/) is applied to a boolean value,
Excel converts TRUE to 1 and FALSE to 0.

The standard convention is to use
the Double-Minus (--) to convert the values.
It works this way:
TRUE=TRUE
-TRUE = -1
--TRUE = 1

FALSE = FALSE
-FALSE = 0
--FALSE = 0

Now, you could easily use 1*TRUE, but the Dbl-Minus indicates to
knowledgable users that you are forcing a conversion and not
trying to calculate something.

So, In the formula, the TRUE/FALSE values are converted to 1's and 0's
by the "--" and the SUMPRODUCT calculates the total.

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)



"S Muzumdar" wrote in message
...
Works like a charm.... Thanks.

Quick question - what does the "--" between two parantheses do ?