Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNIFS
At home I have Excel 2007 but at work we use Excel 2003.
Excel 2007 now has a new function called COUNTIFS. (Counts the number of cells within a range that meet multiple criteria) Obviously when I send the file back to work it no longer works properly. Is there a way to reproduce the function in Excel 2003? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNIFS
=SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3"))
"Larry Byrne" wrote: At home I have Excel 2007 but at work we use Excel 2003. Excel 2007 now has a new function called COUNTIFS. (Counts the number of cells within a range that meet multiple criteria) Obviously when I send the file back to work it no longer works properly. Is there a way to reproduce the function in Excel 2003? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNIFS
I imagine Teethless mama has answered your question, if not the please post
the COUNTIFS formula you're using "Teethless mama" wrote: =SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3")) "Larry Byrne" wrote: At home I have Excel 2007 but at work we use Excel 2003. Excel 2007 now has a new function called COUNTIFS. (Counts the number of cells within a range that meet multiple criteria) Obviously when I send the file back to work it no longer works properly. Is there a way to reproduce the function in Excel 2003? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNIFS
Thank you for your replies but you credit me with too much knowledge.
I understand the €śrange€ť and €ścriteria€ť but what does the -- stand for? I have a table similar to below. A:A B:B C:C 102 Y 1 103 Y 1 116 Y N 117 Y N 102 N N 103 N 1 116 Y N 117 Y N I need two formulas which I understand will be very similar. The first one is where I want to find how many times 102 appears in A:A and where the 102 also has a Y in that row in B:B. The second formula is the same but has the addition that C:C also has a 1 in the same row. When I use COUNTIFS the first formula is =COUNTIFS(April!A:A,B4,April!B:B,"y") the B4 equals the 102 value. Thanks in advance Larry "daddylonglegs" wrote: I imagine Teethless mama has answered your question, if not the please post the COUNTIFS formula you're using "Teethless mama" wrote: =SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3")) "Larry Byrne" wrote: At home I have Excel 2007 but at work we use Excel 2003. Excel 2007 now has a new function called COUNTIFS. (Counts the number of cells within a range that meet multiple criteria) Obviously when I send the file back to work it no longer works properly. Is there a way to reproduce the function in Excel 2003? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNIFS
Hi Larry
=SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3")) The formula as above provided by TM, just needs substituting with your ranges and criteria. =SUMPRODUCT(--(April!$A$1:$A$1000=102),--(April!$B$1:$B$1000="y"),--(April!$C$1:$C$1000=1)) for the three criteria situation. =SUMPRODUCT(--(April!$A$1:$A$1000=102),--(April!$B$1:$B$1000="y")) for the two criteria result. Note, other tan in XL2007, you cannot use whole columns as ranges within Sumproduct. Change the ranges to those sufficient to match your data. The -- (double unary minus) is used to create the True/False response from the tests, to 1/0 which are them summed by Sumproduct to provide the answer. Use this method in XL2007, as it will also work in earlier versions of XL, whereas Countifs in XL2007 specific. -- Regards Roger Govier "Larry Byrne" wrote in message ... Thank you for your replies but you credit me with too much knowledge. I understand the "range" and "criteria" but what does the -- stand for? I have a table similar to below. A:A B:B C:C 102 Y 1 103 Y 1 116 Y N 117 Y N 102 N N 103 N 1 116 Y N 117 Y N I need two formulas which I understand will be very similar. The first one is where I want to find how many times 102 appears in A:A and where the 102 also has a Y in that row in B:B. The second formula is the same but has the addition that C:C also has a 1 in the same row. When I use COUNTIFS the first formula is =COUNTIFS(April!A:A,B4,April!B:B,"y") the B4 equals the 102 value. Thanks in advance Larry "daddylonglegs" wrote: I imagine Teethless mama has answered your question, if not the please post the COUNTIFS formula you're using "Teethless mama" wrote: =SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3")) "Larry Byrne" wrote: At home I have Excel 2007 but at work we use Excel 2003. Excel 2007 now has a new function called COUNTIFS. (Counts the number of cells within a range that meet multiple criteria) Obviously when I send the file back to work it no longer works properly. Is there a way to reproduce the function in Excel 2003? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNIFS
Thank you very much, this now works perfectly for me.
"Roger Govier" wrote: Hi Larry =SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3")) The formula as above provided by TM, just needs substituting with your ranges and criteria. =SUMPRODUCT(--(April!$A$1:$A$1000=102),--(April!$B$1:$B$1000="y"),--(April!$C$1:$C$1000=1)) for the three criteria situation. =SUMPRODUCT(--(April!$A$1:$A$1000=102),--(April!$B$1:$B$1000="y")) for the two criteria result. Note, other tan in XL2007, you cannot use whole columns as ranges within Sumproduct. Change the ranges to those sufficient to match your data. The -- (double unary minus) is used to create the True/False response from the tests, to 1/0 which are them summed by Sumproduct to provide the answer. Use this method in XL2007, as it will also work in earlier versions of XL, whereas Countifs in XL2007 specific. -- Regards Roger Govier "Larry Byrne" wrote in message ... Thank you for your replies but you credit me with too much knowledge. I understand the "range" and "criteria" but what does the -- stand for? I have a table similar to below. A:A B:B C:C 102 Y 1 103 Y 1 116 Y N 117 Y N 102 N N 103 N 1 116 Y N 117 Y N I need two formulas which I understand will be very similar. The first one is where I want to find how many times 102 appears in A:A and where the 102 also has a Y in that row in B:B. The second formula is the same but has the addition that C:C also has a 1 in the same row. When I use COUNTIFS the first formula is =COUNTIFS(April!A:A,B4,April!B:B,"y") the B4 equals the 102 value. Thanks in advance Larry "daddylonglegs" wrote: I imagine Teethless mama has answered your question, if not the please post the COUNTIFS formula you're using "Teethless mama" wrote: =SUMPRODUCT(--(rng1="criteria1"),--(rng2="criteria2"),--(rng3="criteria3")) "Larry Byrne" wrote: At home I have Excel 2007 but at work we use Excel 2003. Excel 2007 now has a new function called COUNTIFS. (Counts the number of cells within a range that meet multiple criteria) Obviously when I send the file back to work it no longer works properly. Is there a way to reproduce the function in Excel 2003? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|