Count cells

[S Muzumdar]

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.

 

[Ron Coderre]

Try this:

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

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

Regards,

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

I

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

 

[S Muzumdar]

Works like a charm.... Thanks.

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

 

[Ron Coderre]

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

this section: (A1:A10&D110="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)

 

[Bob]

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

 

[Dave Peterson]

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 here:
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]

Yes, that helps. Thanks a lot.

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

this section: (A1:A10&D110="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)