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

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
--
HTH...

Jim Thomlinson


"A Newton" wrote:

> 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
>
>

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" wrote:

> 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
>
>

On Jan 30, 5:37 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:
> 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
> --
> HTH...
>
> Jim Thomlinson
>
> "A Newton" wrote:
> > 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

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

On Jan 30, 9:41 pm, JMB <J...@discussions.microsoft.com> wrote:
> 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...ed.htm#Dynamic
>
> "A Newton" wrote:
> > 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

Thanks, the suggestion to use 65535 works out well.