another question
 

Thanks guys - those were all so helpful. I have another question now. How
can I pull multiple information from one column with the same count if. For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!

"EJV" wrote:

I have a column with dates and then a column with 1s and 0s. I want to count
how many of the rows that have "5/11/09" in column B also have a "1" in
column BC. I have been reading the suggestions in here and this is the
formula that I have been trying to use. (Note: Sheet 2 contains all of my
data. I am trying to pull it into a report on Sheet 1.) It keeps coming
back with zero, but I know that there are some. I have checked my data to
make sure that it doesn't contain any spaces.

=SUMPRODUCT((Sheet2!B2:B1242="05/11/2009")*(Sheet2!BC2:BC1242="1"))

Thanks a million!!

another question
 

EJV wrote:
Thanks guys - those were all so helpful. I have another question now. How
can I pull multiple information from one column with the same count if. For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!



Try this:

=SUMPRODUCT((B2:B1000=--"5/11/2009")*(ISNUMBER(FIND({"assoc","a.a.s.c.","managem ent"},D2:D1000))))

another question
 

You can't use wildcards with SUMPRODUCT, but you can do it like this:

=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,5,11))*(She et2!BC2:BC1242=1)*
((ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242))+ISNUMB ER(SEARCH
("a.a.s",Sheet2!D2:D1242))+ISNUMBER(SEARCH("manage ment",Sheet2!
D2:D1242))))

Note that the * is equivalent to AND, the + is equivalent to OR. Be
careful with all the brackets (parentheses).

Hope this helps.

Pete

On Jan 28, 8:20*pm, EJV wrote:
Thanks guys - those were all so helpful. *I have another question now. *How
can I pull multiple information from one column with the same count if. *For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!



"EJV" wrote:
I have a column with dates and then a column with 1s and 0s. *I want to count
how many of the rows that have "5/11/09" in column B also have a "1" in
column BC. *I have been reading the suggestions in here and this is the
formula that I have been trying to use. *(Note: *Sheet 2 contains all of my
data. *I am trying to pull it into a report on Sheet 1.) *It keeps coming
back with zero, but I know that there are some. *I have checked my data to
make sure that it doesn't contain any spaces.

=SUMPRODUCT((Sheet2!B2:B1242="05/11/2009")*(Sheet2!BC2:BC1242="1"))

Thanks a million!!- Hide quoted text -

- Show quoted text -

another question
 

=SUMPRODUCT( (Sheet2!B2:B1242=DATE(2009,5,11)) *
(ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242,1))) )
That will find *assoc*

=SUMPRODUCT( (Sheet2!B2:B1242=DATE(2009,5,11)) *
(ISNUMBER(SEARCH("a.a.s",Sheet2!D2:D1242,1))) )
Thnat finds *a.a.s*

So

=SUMPRODUCT( (Sheet2!B2:B1242=DATE(2009,5,11)) *
(ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242,1))) ) + SUMPRODUCT(
(Sheet2!B2:B1242=DATE(2009,5,11)) *
(ISNUMBER(SEARCH("a.a.s",Sheet2!D2:D1242,1))) )
Will finds both

We can combine
=SUMPRODUCT( (Sheet2!B2:B1242=DATE(2009,5,11)) * (
(ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242,1))) +
(ISNUMBER(SEARCH("a.a.s",Sheet2!D2:D1242,1))) )

I will let you check the paretheses count !
Not that we use * when we want AND and + when we want OR

best wishes

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"EJV" wrote in message
...
Thanks guys - those were all so helpful. I have another question now.
How
can I pull multiple information from one column with the same count if.
For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!

"EJV" wrote:

I have a column with dates and then a column with 1s and 0s. I want to
count
how many of the rows that have "5/11/09" in column B also have a "1" in
column BC. I have been reading the suggestions in here and this is the
formula that I have been trying to use. (Note: Sheet 2 contains all of
my
data. I am trying to pull it into a report on Sheet 1.) It keeps coming
back with zero, but I know that there are some. I have checked my data
to
make sure that it doesn't contain any spaces.

=SUMPRODUCT((Sheet2!B2:B1242="05/11/2009")*(Sheet2!BC2:BC1242="1"))

Thanks a million!!

another question
 

Thank you!! That totally worked. Now I have another question and I would
promise that it is the last, but that probably wouldn't be the truth.

Is there a way that I can now count the cells that are 5/11/09 and not equal
to the criteria that I mentioned before (assoc, a.a.s., management)? I now
need a cell that pulls everything else. Does that make sense - I need a
"not equal to" formula.

I can't express how helpful you have been.

"Pete_UK" wrote:

You can't use wildcards with SUMPRODUCT, but you can do it like this:

=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,5,11))*(She et2!BC2:BC1242=1)*
((ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242))+ISNUMB ER(SEARCH
("a.a.s",Sheet2!D2:D1242))+ISNUMBER(SEARCH("manage ment",Sheet2!
D2:D1242))))

Note that the * is equivalent to AND, the + is equivalent to OR. Be
careful with all the brackets (parentheses).

Hope this helps.

Pete

On Jan 28, 8:20 pm, EJV wrote:
Thanks guys - those were all so helpful. I have another question now. How
can I pull multiple information from one column with the same count if. For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!



"EJV" wrote:
I have a column with dates and then a column with 1s and 0s. I want to count
how many of the rows that have "5/11/09" in column B also have a "1" in
column BC. I have been reading the suggestions in here and this is the
formula that I have been trying to use. (Note: Sheet 2 contains all of my
data. I am trying to pull it into a report on Sheet 1.) It keeps coming
back with zero, but I know that there are some. I have checked my data to
make sure that it doesn't contain any spaces.

=SUMPRODUCT((Sheet2!B2:B1242="05/11/2009")*(Sheet2!BC2:BC1242="1"))

Thanks a million!!- Hide quoted text -

- Show quoted text -

another question
 

Hi,

Count the dates and subtract the formula you already have

=COUNTIF(B2:B1000,"5/11/2009")-(SUMPRODUCT((B2:B1000=--"5/11/2009")*(ISNUMBER(FIND({"assoc","a.a.s.c.","managem ent"},D2:D1000)))))

Mike

"EJV" wrote:

Thank you!! That totally worked. Now I have another question and I would
promise that it is the last, but that probably wouldn't be the truth.

Is there a way that I can now count the cells that are 5/11/09 and not equal
to the criteria that I mentioned before (assoc, a.a.s., management)? I now
need a cell that pulls everything else. Does that make sense - I need a
"not equal to" formula.

I can't express how helpful you have been.

"Pete_UK" wrote:

You can't use wildcards with SUMPRODUCT, but you can do it like this:

=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,5,11))*(She et2!BC2:BC1242=1)*
((ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242))+ISNUMB ER(SEARCH
("a.a.s",Sheet2!D2:D1242))+ISNUMBER(SEARCH("manage ment",Sheet2!
D2:D1242))))

Note that the * is equivalent to AND, the + is equivalent to OR. Be
careful with all the brackets (parentheses).

Hope this helps.

Pete

On Jan 28, 8:20 pm, EJV wrote:
Thanks guys - those were all so helpful. I have another question now. How
can I pull multiple information from one column with the same count if. For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!



"EJV" wrote:
I have a column with dates and then a column with 1s and 0s. I want to count
how many of the rows that have "5/11/09" in column B also have a "1" in
column BC. I have been reading the suggestions in here and this is the
formula that I have been trying to use. (Note: Sheet 2 contains all of my
data. I am trying to pull it into a report on Sheet 1.) It keeps coming
back with zero, but I know that there are some. I have checked my data to
make sure that it doesn't contain any spaces.

=SUMPRODUCT((Sheet2!B2:B1242="05/11/2009")*(Sheet2!BC2:BC1242="1"))

Thanks a million!!- Hide quoted text -

- Show quoted text -

another question
 

In Excel 2007 you could use

=SUM(COUNTIFS(B1:B19,G1,D1:D19,{"*assoc*";"*a.a.s. *";"*management*"}))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"EJV" wrote:

Thanks guys - those were all so helpful. I have another question now. How
can I pull multiple information from one column with the same count if. For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!

"EJV" wrote:

I have a column with dates and then a column with 1s and 0s. I want to count
how many of the rows that have "5/11/09" in column B also have a "1" in
column BC. I have been reading the suggestions in here and this is the
formula that I have been trying to use. (Note: Sheet 2 contains all of my
data. I am trying to pull it into a report on Sheet 1.) It keeps coming
back with zero, but I know that there are some. I have checked my data to
make sure that it doesn't contain any spaces.

=SUMPRODUCT((Sheet2!B2:B1242="05/11/2009")*(Sheet2!BC2:BC1242="1"))

Thanks a million!!

another question
 

Hi,

In my previous post I failed to mention that you should enter the date in G1.

In 2003 you can use the following:

=SUMPRODUCT(ISNUMBER(FIND(TRANSPOSE({"assoc";"a.a. s.";"management"}),D1:D19))*(B1:B19=G1))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"EJV" wrote:

Thank you!! That totally worked. Now I have another question and I would
promise that it is the last, but that probably wouldn't be the truth.

Is there a way that I can now count the cells that are 5/11/09 and not equal
to the criteria that I mentioned before (assoc, a.a.s., management)? I now
need a cell that pulls everything else. Does that make sense - I need a
"not equal to" formula.

I can't express how helpful you have been.

"Pete_UK" wrote:

You can't use wildcards with SUMPRODUCT, but you can do it like this:

=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,5,11))*(She et2!BC2:BC1242=1)*
((ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242))+ISNUMB ER(SEARCH
("a.a.s",Sheet2!D2:D1242))+ISNUMBER(SEARCH("manage ment",Sheet2!
D2:D1242))))

Note that the * is equivalent to AND, the + is equivalent to OR. Be
careful with all the brackets (parentheses).

Hope this helps.

Pete

On Jan 28, 8:20 pm, EJV wrote:
Thanks guys - those were all so helpful. I have another question now. How
can I pull multiple information from one column with the same count if. For
instance, what if I want all of the 5/11/09 (column B) that contain these
words in Column D "*assoc*" or "*a.a.s.* or "*management*".

Thanks again!!!



"EJV" wrote:
I have a column with dates and then a column with 1s and 0s. I want to count
how many of the rows that have "5/11/09" in column B also have a "1" in
column BC. I have been reading the suggestions in here and this is the
formula that I have been trying to use. (Note: Sheet 2 contains all of my
data. I am trying to pull it into a report on Sheet 1.) It keeps coming
back with zero, but I know that there are some. I have checked my data to
make sure that it doesn't contain any spaces.

=SUMPRODUCT((Sheet2!B2:B1242="05/11/2009")*(Sheet2!BC2:BC1242="1"))

Thanks a million!!- Hide quoted text -

- Show quoted text -