Can I use COUNTIF and AND together?

[A Newton]

I have a two-sheet workbook. On one sheet (Worksheet 1), I want to
count the total cells in the other worksheet that contain the words
"Joe Blow" in column J and the word "New" in column H. I was hacking
away trying something like this:

=COUNTIF('Worksheet 2'!J2:J500,"Joe Blow") AND ('Worksheet 2'!
H2:H500,"New")

But no dice. . .

Also, is there an easy way to say "all of column J" and "all of column
H" instead of using the range as I've done above. (I just said 500
'cuz it was well beyond the end of the data range.)

Thanks in advance for suggestions/help.

-- Adam

 

[Jim Thomlinson]

You probably want...

=SumProduct(('Worksheet 2'!J2:J500="Joe Blow") * ('Worksheet 2'!
H2:H500="New"))

Check out this link for more info...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[JMB]

Normally you would reference an entire column as J:J, but sumproduct cannot
work with an entire column (although I think it will if you are using
XL2007). You could use J2:J65536 or J2:J65535, however. Or, you could set
up dynamic named ranges.

Chip has some info here as well as a downloadable example:
http://www.cpearson.com/excel/excelF.htm#DynamicRanges
http://www.cpearson.com/excel/named.htm#Dynamic

 

[A Newton]

You probably want...

=SumProduct(('Worksheet 2'!J2:J500="Joe Blow") * ('Worksheet 2'!
H2:H500="New"))

Check out this link for more info...http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Thanks for the help, Jim. Your suggestion to use SUMPRODUCT worked out
perfectly.

 

[A Newton]

Normally you would reference an entire column as J:J, but sumproduct cannot
work with an entire column (although I think it will if you are using
XL2007). You could use J2:J65536 or J2:J65535, however. Or, you could set
up dynamic named ranges.

Chip has some info here as well as a downloadable example:http://www.cpearson.com/excel/excel...tp://www.cpearson.com/excel/named.htm#Dynamic

Thanks, the suggestion to use 65535 works out well.