find occurance of something from 2 creterion
 

If I have 2 columns like below:

A,2008
A,2007
C,2007
B,2008
C,2007
A,2008
A,2008
B,2008

I can use COUNTIF(A1:A8,A11) to find the total occurance of A, but how
can I add one moe creterion so I can get the result that it's A and
also 2008 (2nd column). So the answer should be 3 in the example
above.

Thanks for the help,

liu

find occurance of something from 2 creterion
 

In all Excel versions: =SUMPRODUCT(--(A1:A100="A"),--(B1:B100=2008))
Only in Excel 2007 : =SUMPRODUCT(--(A:A="A"),--(B:B=2008))
Only in Excel 2007: =COUNTIFS(A1:A100,"A",B1:B100,2008)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"liu" wrote in message
...
If I have 2 columns like below:

A,2008
A,2007
C,2007
B,2008
C,2007
A,2008
A,2008
B,2008

I can use COUNTIF(A1:A8,A11) to find the total occurance of A, but how
can I add one moe creterion so I can get the result that it's A and
also 2008 (2nd column). So the answer should be 3 in the example
above.

Thanks for the help,

liu

find occurance of something from 2 creterion
 

On Feb 5, 1:33*pm, "Bernard Liengme"
wrote:
In all Excel versions: =SUMPRODUCT(--(A1:A100="A"),--(B1:B100=2008))
Only in Excel 2007 : =SUMPRODUCT(--(A:A="A"),--(B:B=2008))
Only in Excel 2007: =COUNTIFS(A1:A100,"A",B1:B100,2008)
best wishes

That works. Thank you so much for the hlep.

find occurance of something from 2 creterion
 

On Feb 5, 2:02*pm, liu wrote:
On Feb 5, 1:33*pm, "Bernard Liengme"
wrote:

In all Excel versions: =SUMPRODUCT(--(A1:A100="A"),--(B1:B100=2008))
Only in Excel 2007 : =SUMPRODUCT(--(A:A="A"),--(B:B=2008))
Only in Excel 2007: =COUNTIFS(A1:A100,"A",B1:B100,2008)
best wishes

That works. Thank you so much for the hlep.

BTW, what does "--" mean in the function?

Thanks for the help.

find occurance of something from 2 creterion
 

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html
--
David Biddulph

"liu" wrote in message
...

BTW, what does "--" mean in the function?

Thanks for the help.

On Feb 5, 2:02 pm, liu wrote:
On Feb 5, 1:33 pm, "Bernard Liengme"
wrote:

In all Excel versions: =SUMPRODUCT(--(A1:A100="A"),--(B1:B100=2008))
Only in Excel 2007 : =SUMPRODUCT(--(A:A="A"),--(B:B=2008))
Only in Excel 2007: =COUNTIFS(A1:A100,"A",B1:B100,2008)
best wishes

That works. Thank you so much for the hlep.