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

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


=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,5,11))*(She et2!BC2:BC1242=1))

Assuming that the dates are actually dates (and 1s and 0s are numbers) and not text.

Hi,

Try this

=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,11,5))*(She et2!BC2:BC1242=1))

Note the way the date is now defined and the quotes are gone from around the 1

Mike

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

Hi,

If you are using 2007
=SUMPRODUCT(--(DATE(2009,5,11)=B:B),-(BC:BC=1))

=SUMPRODUCT(--(DATE(2009,5,11)=B1:B10),--(BC1:BC10=1))

If you are using an earlier version don't reference the entire column.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


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

You are so awesome!!! Thank you, thank you!!!

"Glenn" wrote:

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


=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,5,11))*(She et2!BC2:BC1242=1))

Assuming that the dates are actually dates (and 1s and 0s are numbers) and not text.

Hi,

Also in 2007 you can use

=COUNTIFS(B:B,DATE(2009,5,11),BC:BC,1)

or in all cases you could replace the DATE(2009,5,11) portion with

--"5/11/09"

or better yet enter the date in A1 and the formula becomes

=COUNTIFS(B:B,A1,BC:BC,1)
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


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

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