Adding onto the COUNTIF function 1

[Hfnet]

I have a spreadsheet that currently shows how many instances of a name occur in column B and match the name in Column L and the row in which the name ocurrs:

COUNTIF($B$2:$B$50,L2)

This works fine, but I want to filter that for where Column C has a value of 4 (currently the values range from 1 to 6 and I want to ignore counts of the names unless the value in the next column is 4)

Any suggestions please?

 

[GlennUK]

Use SUMPRODUCT with logical tests like this:

=SUMPRODUCT(($B$2:$B$50=L2)*($C$2:$C$50=4))

Cheers, Glenn.

 

[SkipVought]

Hi,

Try the SUMPRODUCT function
[tt]
=SUMPRODUCT(($B$2:$B$50,L2)*($C$2:$C$50,4))/4
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[Hfnet]

Thanks GlennUK, worked a treat, the other option seems to have an error in the code, but thanks anyway guys