countif??

[lr822]

I am trying to do a formula to count the number of rows that have a certain value in two different columns.

For example, if the person in row 1 has a value of 3 in column b and 1 in column c, I want them counted.

I have done =COUNTIF(B:B,3)+COUNTIF(C:C,1), but that counts all of the people that have 3's in column b and adds them to those that have 1's in column c. I only want them counted only if both conditions are met.

 

[VoG]

Try

=SUMPRODUCT(--(B1:B100=3),--(C1:C100=1))

 

[MrKowz]

If you use Excel 2003 or earlier:

=SUMPRODUCT(--(B1:B100=3),--(C1:C100=1))

If you use Excel 2007 or later:

=COUNTIFS(B1:B100,3,C1:C100,1)

 

[lr822]

Thanks. But neither of those worked. The countifs came back with "name?" error and the sumproduct came back with 0, when there are values there. I'm sure it's something simple I'm doing wrong, but can't figure it out.

 

[VoG]

What does

=ISNUMBER(B1) return?

 

[lr822]

True

 

[VoG]

It works for me

Excel Workbook
	B	C	D
1	3	2	2
2	3	1
3	2	1
4	3	1
Sheet4