Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use COUNTIF and AND together?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use COUNTIF and AND together?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use COUNTIF and AND together?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use COUNTIF and AND together?
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can I use COUNTIF and AND together?
On Jan 30, 9:41 pm, JMB 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using COUNTIF with AND | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |