CountIF Based on 2 Variables

[Richard - ExcelForums.com]

Hello

I have a table in which I have data that looks like the followin

18:00 NYK SONY 1 0

Basically I want to search the table for the number of rows that hav
NYK and N in them

I though about countif() but that only seems to work on one variabl
not the too i want

Cheers for any help
Richar

 

[Alan]

With 'NYK' in column B and 'N' in column 'E'.
=SUMPRODUCT((A1:A5000="NYK")*(E1:E5000="N"))
or
=SUMPRODUCT(--(A1:A5000="NYK"),-(E1:E5000="N"))
You can put the search values in a cell eg in D1 enter NYK and in E1 enter
N. The formula would now be
=SUMPRODUCT((A1:A5000=D1)*(E1:E5000=E1))
or
=SUMPRODUCT(--(A1:A5000=D1),-(E1:E5000=E1))
Regards,

 

[Henrik]

Try,
{=SUM(1*ISNUMBER(SEARCH("NYK",$B:$B))*ISNUMBER(SEARCH("N",$F:$F))}

This is an array formula that you have to submit by pressing ctrl + shift +
enter

good luck,
Henrik

 

[Bernard Liengme]

Try this =SUMPRODUCT(--(B1:B20="NYK"),--(F1:F20="N"))