If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Counting using multiple criteria
Fantastic! I was looking for this same solution. While I do NOT understand
how it works, it works. I had this issue resolve in 5 minutes with Excel 2007, but we use 2003 at work, so this helps out a lot. Still don't understand how the '--' works..... Thanks. "Toppers" wrote: See Bob's reply: =SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete")) Assumes "Jan" is TEXT not date format "Rodman" wrote: Let me be a bit more specific. A spreadsheet contains the following data: A B C Jan 2006 Complete Jan 2006 Complete Feb 2006 Hold Dec 2006 In Progress . . . Jan 2007 Complete Jan 2007 Hold I would like a count (for example): of the number Complete in Jan of 2006. (i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is complete then increment a counter - like ANDing 3 countif functions [i.e., countif(a2:a50,"Jan") and countif(b2:b50, "2006") and countif(c2:c50,"Complete")]. I hope this example bettter expreses the function I am trying to perform. Thanks In Advance - Again -- Rodman Veney "Rodman" wrote: I am using Excel 2003 and I would like to count the number of cells within a range thar meet multiple criteria. (Note this function is availiable in 2007 as COUNTIFS). Can anyone help? Thanks In Advance -- Rodman Veney |
#12
|
|||
|
|||
Counting using multiple criteria
Still don't understand how the '--' works
See this: http://xldynamic.com/source/xld.SUMPRODUCT.html -- Biff Microsoft Excel MVP "Tom" wrote in message ... Fantastic! I was looking for this same solution. While I do NOT understand how it works, it works. I had this issue resolve in 5 minutes with Excel 2007, but we use 2003 at work, so this helps out a lot. Still don't understand how the '--' works..... Thanks. "Toppers" wrote: See Bob's reply: =SUMPRODUCT(--(A2:A100="Jan"),--(B2:B100=2006),--(C2:C100="Complete")) Assumes "Jan" is TEXT not date format "Rodman" wrote: Let me be a bit more specific. A spreadsheet contains the following data: A B C Jan 2006 Complete Jan 2006 Complete Feb 2006 Hold Dec 2006 In Progress . . . Jan 2007 Complete Jan 2007 Hold I would like a count (for example): of the number Complete in Jan of 2006. (i.e., if month (col A) is Jan and year (Col B) is 2006 and status (Col C) is complete then increment a counter - like ANDing 3 countif functions [i.e., countif(a2:a50,"Jan") and countif(b2:b50, "2006") and countif(c2:c50,"Complete")]. I hope this example bettter expreses the function I am trying to perform. Thanks In Advance - Again -- Rodman Veney "Rodman" wrote: I am using Excel 2003 and I would like to count the number of cells within a range thar meet multiple criteria. (Note this function is availiable in 2007 as COUNTIFS). Can anyone help? Thanks In Advance -- Rodman Veney |
|
Thread Tools | |
Display Modes | |
|
|