Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif compares to Countifs
How can I use a count function in excel 2003 that does the same thing as
countifs in Excel 2007? I created this multi criteria in Excel 2007 but it wont work in Excel 2003 as the rest of the team dont have the same version and it converts to #NAME?. I am trying to count the number of times where a particular criteria has been used e.g. 12 Mth where there is a date in column AC =_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") How can I use mutilpe criteria in Excel 2003 to do the same thing? -- kje.1953 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif compares to Countifs
Use
=SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$1500)) "kje.1953" wrote: How can I use a count function in excel 2003 that does the same thing as countifs in Excel 2007? I created this multi criteria in Excel 2007 but it wont work in Excel 2003 as the rest of the team dont have the same version and it converts to #NAME?. I am trying to count the number of times where a particular criteria has been used e.g. 12 Mth where there is a date in column AC =_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") How can I use mutilpe criteria in Excel 2003 to do the same thing? -- kje.1953 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif compares to Countifs
Thank you for getting back to me so soon. I tried it but it essentially just
counted the number of 'products" which Countif does already. What I am trying to do is where there is a date (as they accepted the offer) to look at the product & give me a count. Products are in colum AB, date/acceptance is in column AC I got the results when I used countifs but somehow I am not getting it this time. Can you help me? -- kje.1953 "Sheeloo" wrote: Use =SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$1500)) "kje.1953" wrote: How can I use a count function in excel 2003 that does the same thing as countifs in Excel 2007? I created this multi criteria in Excel 2007 but it wont work in Excel 2003 as the rest of the team dont have the same version and it converts to #NAME?. I am trying to count the number of times where a particular criteria has been used e.g. 12 Mth where there is a date in column AC =_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") How can I use mutilpe criteria in Excel 2003 to do the same thing? -- kje.1953 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif compares to Countifs
It IS counting the products where they match "12 mth" AND where corresponding
cell in AC is not blank ("0") Test it out after deleting everything from Col AC then entering dates one by one... You will see that the count starts at 1 and increases as you enter the dates... I tested again and it is working... It it does not work then pl. put X in all blank cells in AC and change the condition to <"X" and see if it works... Do you have formulas in AC? "kje.1953" wrote: Thank you for getting back to me so soon. I tried it but it essentially just counted the number of 'products" which Countif does already. What I am trying to do is where there is a date (as they accepted the offer) to look at the product & give me a count. Products are in colum AB, date/acceptance is in column AC I got the results when I used countifs but somehow I am not getting it this time. Can you help me? -- kje.1953 "Sheeloo" wrote: Use =SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$1500)) "kje.1953" wrote: How can I use a count function in excel 2003 that does the same thing as countifs in Excel 2007? I created this multi criteria in Excel 2007 but it wont work in Excel 2003 as the rest of the team dont have the same version and it converts to #NAME?. I am trying to count the number of times where a particular criteria has been used e.g. 12 Mth where there is a date in column AC =_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") How can I use mutilpe criteria in Excel 2003 to do the same thing? -- kje.1953 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif compares to Countifs
=SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$1500))
Try changing 0 to ISNUMBER: =SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--(ISNUMBER($AC$3:$AC$150))) -- Biff Microsoft Excel MVP "Sheeloo" wrote: It IS counting the products where they match "12 mth" AND where corresponding cell in AC is not blank ("0") Test it out after deleting everything from Col AC then entering dates one by one... You will see that the count starts at 1 and increases as you enter the dates... I tested again and it is working... It it does not work then pl. put X in all blank cells in AC and change the condition to <"X" and see if it works... Do you have formulas in AC? "kje.1953" wrote: Thank you for getting back to me so soon. I tried it but it essentially just counted the number of 'products" which Countif does already. What I am trying to do is where there is a date (as they accepted the offer) to look at the product & give me a count. Products are in colum AB, date/acceptance is in column AC I got the results when I used countifs but somehow I am not getting it this time. Can you help me? -- kje.1953 "Sheeloo" wrote: Use =SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$1500)) "kje.1953" wrote: How can I use a count function in excel 2003 that does the same thing as countifs in Excel 2007? I created this multi criteria in Excel 2007 but it wont work in Excel 2003 as the rest of the team dont have the same version and it converts to #NAME?. I am trying to count the number of times where a particular criteria has been used e.g. 12 Mth where there is a date in column AC =_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") How can I use mutilpe criteria in Excel 2003 to do the same thing? -- kje.1953 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif compares to Countifs
Hi,
Lets go back to your first question, how can us use a COUNT function in 2003 to do the same thing as COUNTIFS in 2007. You can but you need to use the DCOUNT or DCOUNTA functions. The COUNT, COUNTIF, COUNTBLANK function can't duplicate the functionality of COUNTIF or there wouldn't be a COUNTIFS function, it would be redundant. It gives you a NAME error message in 2003 because that function doesn't exist in 2003, it is one of the 12 new functions introduced in 2007. COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") This formula appears to be counting all the rows which contain 12 mth in column AB and are greater than 0 in column AC. To do this with DCOUNTA assume you have titles on row 2. In some empty cells enter the exact titles found in AB2 and AC2, I'm going to call those Month and Amount for the sake of this example. Below these to titles, which I will assume you are going to put in the empty cell AM1 and AN1 enter your two criteria, the result would look like this: AM AN 1 Month Amount 2 12 mth 0 Now enter the following formula in an empty cell: =DCOUNTA(AB2:AC150,1,AM1:AN2) You can also use a SUMPRODUCT function like the one previously suggested: =SUMPRODUCT(--(AB3:AB150="12 mth"),--(AC3:AC1500)) Now let's suppose this doesn't work, then I must ask what is 12 mth? It sounds like you are refering to a DATE but you are showing us a TEXT entry. If it is a legal Excel date then the DCOUNTA and SUMPRODUCT functions will fail. Both of these functions would return 0. If the entries really are 12 mth text then both functions will return all the rows that match on both condtions at the same time. They will not count the items if they only match one criterial, however, neither will COUNTIFS. If this helps, please click the Yes button cheers, Shane Devenshire "kje.1953" wrote: How can I use a count function in excel 2003 that does the same thing as countifs in Excel 2007? I created this multi criteria in Excel 2007 but it wont work in Excel 2003 as the rest of the team dont have the same version and it converts to #NAME?. I am trying to count the number of times where a particular criteria has been used e.g. 12 Mth where there is a date in column AC =_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") How can I use mutilpe criteria in Excel 2003 to do the same thing? -- kje.1953 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Countif compares to Countifs
Thank you so much everyone for your assistance. I have used the
=SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--(ISNUMBER($AC$3:$AC$150))) which worked best on the totals. You guys are all abosultely amazing. I have learnt so much. -- kje.1953 "T. Valko" wrote: =SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$1500)) Try changing 0 to ISNUMBER: =SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--(ISNUMBER($AC$3:$AC$150))) -- Biff Microsoft Excel MVP "Sheeloo" wrote: It IS counting the products where they match "12 mth" AND where corresponding cell in AC is not blank ("0") Test it out after deleting everything from Col AC then entering dates one by one... You will see that the count starts at 1 and increases as you enter the dates... I tested again and it is working... It it does not work then pl. put X in all blank cells in AC and change the condition to <"X" and see if it works... Do you have formulas in AC? "kje.1953" wrote: Thank you for getting back to me so soon. I tried it but it essentially just counted the number of 'products" which Countif does already. What I am trying to do is where there is a date (as they accepted the offer) to look at the product & give me a count. Products are in colum AB, date/acceptance is in column AC I got the results when I used countifs but somehow I am not getting it this time. Can you help me? -- kje.1953 "Sheeloo" wrote: Use =SUMPRODUCT(--($AB$3:$AB$150="12 mth"),--($AC$3:$AC$1500)) "kje.1953" wrote: How can I use a count function in excel 2003 that does the same thing as countifs in Excel 2007? I created this multi criteria in Excel 2007 but it wont work in Excel 2003 as the rest of the team dont have the same version and it converts to #NAME?. I am trying to count the number of times where a particular criteria has been used e.g. 12 Mth where there is a date in column AC =_xlfn.COUNTIFS($AB$3:$AB$150,"12 mth",$AC$3:$AC$150,"0") How can I use mutilpe criteria in Excel 2003 to do the same thing? -- kje.1953 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fix EXACT function so it always compares in the same row. | Excel Worksheet Functions | |||
Replacing COUNTIFS with COUNTIF | Excel Worksheet Functions | |||
create a macro that compares two workbooks | Excel Discussion (Misc queries) | |||
Function that compares dates | Excel Worksheet Functions | |||
formula that compares different ranges | Excel Worksheet Functions |