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.

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.

Works like a charm.... Thanks.

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

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 ?

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 ?

=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.

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