Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF to count based on multiple cell criteria.
Hi, I have the following sample data
I have data identifing (1) the description of a document issued (column ranged named SI_DES) and (2) the date it was issued (Column range called ISSUED). I am able to use the COUNTIF function to count the number of times the term "OSH" occurs in the description to determine the number of OSH documents are identified in the total list. - The formula I use is =COUNTIF(SI_DES,"*OSH*"). I am also able to count the number of if times any given document is issued per month - The formula I use is =(COUNTIF(ISSUED,"=3/1/07")-COUNTIF(ISSUED,"3/31/07")) What I am having troble with is counting the number of OSH documents that are issued in a given month. I am having troble combing the two formulas above to generate to answer. If any one can help, it would be much appreciated. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF to count based on multiple cell criteria.
Try
=SUMPRODUCT(--(YEAR(ISSUED)=2007),--(MONTH(ISSUED)=3),--(ISNUMBER(SEARCH("OSH",SI_DES)))) -- Regards, Peo Sjoblom "Disdan" wrote in message ... Hi, I have the following sample data I have data identifing (1) the description of a document issued (column ranged named SI_DES) and (2) the date it was issued (Column range called ISSUED). I am able to use the COUNTIF function to count the number of times the term "OSH" occurs in the description to determine the number of OSH documents are identified in the total list. - The formula I use is =COUNTIF(SI_DES,"*OSH*"). I am also able to count the number of if times any given document is issued per month - The formula I use is =(COUNTIF(ISSUED,"=3/1/07")-COUNTIF(ISSUED,"3/31/07")) What I am having troble with is counting the number of OSH documents that are issued in a given month. I am having troble combing the two formulas above to generate to answer. If any one can help, it would be much appreciated. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF to count based on multiple cell criteria.
Try this:
=SUMPRODUCT(--(ISNUMBER(SEARCH("osh",SI_DES))),--(ISSUED=--"3/1/2007"),--(ISSUED<=--"3/31/2007")) Note that SEARCH is not case sensitive. SEARCH will work for both "osh" and "OSH". If you explicitly want to match uppercase "OSH" replace SEARCH with FIND: =SUMPRODUCT(--(ISNUMBER(FIND("OSH",SI_DES))),--(ISSUED=--"3/1/2007"),--(ISSUED<=--"3/31/2007")) -- Biff Microsoft Excel MVP "Disdan" wrote in message ... Hi, I have the following sample data I have data identifing (1) the description of a document issued (column ranged named SI_DES) and (2) the date it was issued (Column range called ISSUED). I am able to use the COUNTIF function to count the number of times the term "OSH" occurs in the description to determine the number of OSH documents are identified in the total list. - The formula I use is =COUNTIF(SI_DES,"*OSH*"). I am also able to count the number of if times any given document is issued per month - The formula I use is =(COUNTIF(ISSUED,"=3/1/07")-COUNTIF(ISSUED,"3/31/07")) What I am having troble with is counting the number of OSH documents that are issued in a given month. I am having troble combing the two formulas above to generate to answer. If any one can help, it would be much appreciated. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF to count based on multiple cell criteria.
Hi Peo,
Thanks for yout help, I tried the function you suggested and it gives me the #NUM error. SI_DES column is text and ISSUED is date. I think this is why the function returned this error. Below is some sample data to better demonstrate what I am trying to do. SI_DES(text format) ISSUED(date format) OSH1 1/1/2007 doc1 1/5/2007 OSH2 1/6/2007 OSH3 1/25/2007 doc2 2/3/2007 OSH4 2/5/2007 The function I am trying to create is to answer is "how many OSH documents were issued in January 2007". The answer is 3. I hope this better clarifies the problem Thanks - David "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(YEAR(ISSUED)=2007),--(MONTH(ISSUED)=3),--(ISNUMBER(SEARCH("OSH",SI_DES)))) -- Regards, Peo Sjoblom "Disdan" wrote in message ... Hi, I have the following sample data I have data identifing (1) the description of a document issued (column ranged named SI_DES) and (2) the date it was issued (Column range called ISSUED). I am able to use the COUNTIF function to count the number of times the term "OSH" occurs in the description to determine the number of OSH documents are identified in the total list. - The formula I use is =COUNTIF(SI_DES,"*OSH*"). I am also able to count the number of if times any given document is issued per month - The formula I use is =(COUNTIF(ISSUED,"=3/1/07")-COUNTIF(ISSUED,"3/31/07")) What I am having troble with is counting the number of OSH documents that are issued in a given month. I am having troble combing the two formulas above to generate to answer. If any one can help, it would be much appreciated. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF to count based on multiple cell criteria.
No the num error is returned by other reasons, if the arrays are different
in size it will be returned or if the named ranges you are using are defining the whole column, meaning that A1:A65535 will work but A:A will not whereas SUMIF will work with whole ranges -- Regards, Peo Sjoblom "Disdan" wrote in message ... Hi Peo, Thanks for yout help, I tried the function you suggested and it gives me the #NUM error. SI_DES column is text and ISSUED is date. I think this is why the function returned this error. Below is some sample data to better demonstrate what I am trying to do. SI_DES(text format) ISSUED(date format) OSH1 1/1/2007 doc1 1/5/2007 OSH2 1/6/2007 OSH3 1/25/2007 doc2 2/3/2007 OSH4 2/5/2007 The function I am trying to create is to answer is "how many OSH documents were issued in January 2007". The answer is 3. I hope this better clarifies the problem Thanks - David "Peo Sjoblom" wrote: Try =SUMPRODUCT(--(YEAR(ISSUED)=2007),--(MONTH(ISSUED)=3),--(ISNUMBER(SEARCH("OSH",SI_DES)))) -- Regards, Peo Sjoblom "Disdan" wrote in message ... Hi, I have the following sample data I have data identifing (1) the description of a document issued (column ranged named SI_DES) and (2) the date it was issued (Column range called ISSUED). I am able to use the COUNTIF function to count the number of times the term "OSH" occurs in the description to determine the number of OSH documents are identified in the total list. - The formula I use is =COUNTIF(SI_DES,"*OSH*"). I am also able to count the number of if times any given document is issued per month - The formula I use is =(COUNTIF(ISSUED,"=3/1/07")-COUNTIF(ISSUED,"3/31/07")) What I am having troble with is counting the number of OSH documents that are issued in a given month. I am having troble combing the two formulas above to generate to answer. If any one can help, it would be much appreciated. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using COUNTIF to count based on multiple cell criteria.
OK, I'm no expert, but I had a somewhat similar question, and some guys on
here pointed me in the right direction for me, I made a sample spreadsheet with your data and utilized the formula they helped me develop and it seems to work for your problem. Here it is: =COUNTIFS(A2:A9,"=osh*",B2:B9,"=1/1/2007",B2:B9,"<=1/31/2007") just change the values for A & B for the total number of entries on your sheet. Also make sure to change the date to find the different months you want. So maybe the experts can help you make this simpler to use. OH, keep in mind that this formula is for Excel2007. Hope it helps. Lawrence "Disdan" wrote: Hi, I have the following sample data I have data identifing (1) the description of a document issued (column ranged named SI_DES) and (2) the date it was issued (Column range called ISSUED). I am able to use the COUNTIF function to count the number of times the term "OSH" occurs in the description to determine the number of OSH documents are identified in the total list. - The formula I use is =COUNTIF(SI_DES,"*OSH*"). I am also able to count the number of if times any given document is issued per month - The formula I use is =(COUNTIF(ISSUED,"=3/1/07")-COUNTIF(ISSUED,"3/31/07")) What I am having troble with is counting the number of OSH documents that are issued in a given month. I am having troble combing the two formulas above to generate to answer. If any one can help, it would be much appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count with multiple criteria based on cell not blank? | Excel Worksheet Functions | |||
Count based on multiple criteria | Excel Worksheet Functions | |||
How do I get the count in excel based on criteria in multiple col | Excel Worksheet Functions | |||
How do you create COUNTIF functions based on multiple criteria? | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |