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 -