another question

  • Thread starter Thread starter EJV
  • Start date Start date
E

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!!!
 
EJV said:
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!!!
Click to expand...


Try this:

=SUMPRODUCT((B2:B1000=--"5/11/2009")*(ISNUMBER(FIND({"assoc","a.a.s.c.","management"},D2:D1000))))
 
You can't use wildcards with SUMPRODUCT, but you can do it like this:

=SUMPRODUCT((Sheet2!B2:B1242=DATE(2009,5,11))*(Sheet2!BC2:BC1242=1)*
((ISNUMBER(SEARCH("assoc",Sheet2!D2:D1242))+ISNUMBER(SEARCH
("a.a.s",Sheet2!D2:D1242))+ISNUMBER(SEARCH("management",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
 
=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
 
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.
 
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.","management"},D2:D1000)))))

Mike
 
In Excel 2007 you could use

=SUM(COUNTIFS(B1:B19,G1,D1:D19,{"*assoc*";"*a.a.s.*";"*management*"}))
 
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))
 
Back
Top