Counting based on two distinct values

[timtimba]

Hi,

I am trying to count multiple occurences.

Instrument Name</SPAN>	Times Traded</SPAN>
AEON CO LTD</SPAN>	6</SPAN>
ASICS CORP</SPAN>	3</SPAN>
ELECTRIC POWER DEVELOPMENT C</SPAN>	10</SPAN>

<TBODY>
</TBODY>

In column C (Times Traded) I have used a simple countif formula: =COUNTIF($B$2:$B$8068, C2)

Now I have to check for how many times this instrument was bought or sold. This is in column A .... How will I count the Buy values ... cause this formula I devised doesnt work =COUNTIF($B$2:$B$8068, and(C2, A2:A8068="Buy")

Any help will be appreciated

 

[VoG]

Try

=SUMPRODUCT(--(B2:B8068=C2),--(A2:A8068="Buy"))

 

[timtimba]

Try

=SUMPRODUCT(--(B2:B8068=C2),--(A2:A8068="Buy"))

Perfect! Thanks VoG!

 

[timtimba]

can I be of bit more pain and ask you to explain this formula? .... so I know for future reference

 

[VoG]

Have a look here http://www.xldynamic.com/source/xld.SUMPRODUCT.html