Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I have a question | Excel Worksheet Functions | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
Question | Charts and Charting in Excel |