count base on 2 criteria 1

[tikual]

Hi all,

I have a table like this:

Server Brand Type

ABC Sun Server
BCD Sun Tape Drive
EFG Dell Server
JKL Sun Server

Result 2

If the row matches to "Sun" and "Server", then I would like to count it and the result should be 2. I try function "match" or "if" but not success. Any hints can be provided to me?

Thanks a lot!

tikual

 

[straybullet]

In Access, it would look something like this:

SELECT Count(tablename.Brand) AS CountSS
FROM tablename
WHERE (((tablename.Brand)="Sun") AND ((tablename.Type)="Server"));


Let them hate - so long as they fear... Lucius Accius

 

[tikual]

I am sorry about my situation is under Excel. Thanks.

tikual

 

[KenWright]

=SUMPRODUCT(--(B1:B10="Sun"),--(C1:C10="Server"))

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[tikual]

Thanks a lot!! But, could you please let me know what is the meaning of the sign -- before the array? I don't know why it works.

tikual

 

[Rofeu]

Hi,

B1:B10="Sun" returns an array of boolean values. As you want to do calculations with it, the double negative (--) coerces the boolean value to a numerical value. Excel does this automatically, but the formula calculates faster when you help it a bit.

(there are many other ways to coerce to a numerical value, but the double negative seems to have been adopted as the convention nowadays)

Cheers,

Roel

 

[tikual]

Thanks Rofeu, where can I find this information? I still not fully understand its usage.

tikual

 

[GlennUK]

Hi tikual,

for a full explanation see here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[tikual]

thanks Glenn!

tikual