stuck...trying to pull criteria from 2 columns

[EJV]

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

 

[Glenn]

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))*(Sheet2!BC2:BC1242=1))

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

 

[Mike H]

Hi,

Try this

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

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

Mike

 

[Shane Devenshire]

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.

 

[EJV]

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

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

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

 

[Shane Devenshire]

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)

 

[EJV]

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