Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif multiple conditions
Hi All
I'm trying to setup a formula which counts the amount of times 2 particular conditions are true. From what I've read, =countif can only be used for single conditions. I've tried some other options (see below) but all to no avail. Below is an eg of what I'm trying to do. Basically, I have a worksheet (A) with several coulms and 20000 rows. John word Mary excel Ann excel John word Mary word Ann excel I then have another worksheet (B) with just a list of user names John Mary Ann What I want to do is count the amount of times each user has used each app. So the final worksheet (B) would look like word excel John 2 0 Mary 1 1 Ann 0 2 As I say, my list of usernames is over 100 and the total rows is over 20000 so manual counting (even sorting my name) would be a chore. I also tried the below formulae but it doesn't seem to work (found reference from the web and adapted it to suit my needs but may have made a mistke?) =SUM(IF($H2:$H4="John", IF($I2:$I4="word",1,0),0)) Apologies for the long post, if any one can point me in the right direction I'd really appreciate it. regards Gerry |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif multiple conditions
I think a Pivot Table would be the least amount of work....
On Sheet_B: <Data<Pivot Table Use: Excel Select your data from Sheet_A Click the [Options] button €¢ For empty cells show: 0 Click the [Layout] button €¢ ROW: Drag the NAME field here €¢ COLUMN: Drag the APP field here €¢ DATA: Drag the APP field here If it doesn't list as Count of APP...dbl-click it and set it to Count €¢ Click [OK] Select where you want the Pivot Table...and you're done. The end result will look like this: Count of App App Name excel word Grand Total Ann 2 0 2 John 0 2 2 Mary 1 1 2 Grand Total 3 3 6 To refresh the Pivot Table, just right click it and select Refresh Data If the data range will change regularly, base the Pivot Table on a Dynamic Range Name, (which will automatically expand and contract to accommodate the data). For instructions: http://www.contextures.com/xlNames01.html#Dynamic Is that something you can work with? *********** Regards, Ron XL2002, WinXP "gerryR" wrote: Hi All I'm trying to setup a formula which counts the amount of times 2 particular conditions are true. From what I've read, =countif can only be used for single conditions. I've tried some other options (see below) but all to no avail. Below is an eg of what I'm trying to do. Basically, I have a worksheet (A) with several coulms and 20000 rows. John word Mary excel Ann excel John word Mary word Ann excel I then have another worksheet (B) with just a list of user names John Mary Ann What I want to do is count the amount of times each user has used each app. So the final worksheet (B) would look like word excel John 2 0 Mary 1 1 Ann 0 2 As I say, my list of usernames is over 100 and the total rows is over 20000 so manual counting (even sorting my name) would be a chore. I also tried the below formulae but it doesn't seem to work (found reference from the web and adapted it to suit my needs but may have made a mistke?) =SUM(IF($H2:$H4="John", IF($I2:$I4="word",1,0),0)) Apologies for the long post, if any one can point me in the right direction I'd really appreciate it. regards Gerry |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif multiple conditions
Perfect!
Thank you very much! "Ron Coderre" wrote in message ... I think a Pivot Table would be the least amount of work.... On Sheet_B: <Data<Pivot Table Use: Excel Select your data from Sheet_A Click the [Options] button . For empty cells show: 0 Click the [Layout] button . ROW: Drag the NAME field here . COLUMN: Drag the APP field here . DATA: Drag the APP field here If it doesn't list as Count of APP...dbl-click it and set it to Count . Click [OK] Select where you want the Pivot Table...and you're done. The end result will look like this: Count of App App Name excel word Grand Total Ann 2 0 2 John 0 2 2 Mary 1 1 2 Grand Total 3 3 6 To refresh the Pivot Table, just right click it and select Refresh Data If the data range will change regularly, base the Pivot Table on a Dynamic Range Name, (which will automatically expand and contract to accommodate the data). For instructions: http://www.contextures.com/xlNames01.html#Dynamic Is that something you can work with? *********** Regards, Ron XL2002, WinXP "gerryR" wrote: Hi All I'm trying to setup a formula which counts the amount of times 2 particular conditions are true. From what I've read, =countif can only be used for single conditions. I've tried some other options (see below) but all to no avail. Below is an eg of what I'm trying to do. Basically, I have a worksheet (A) with several coulms and 20000 rows. John word Mary excel Ann excel John word Mary word Ann excel I then have another worksheet (B) with just a list of user names John Mary Ann What I want to do is count the amount of times each user has used each app. So the final worksheet (B) would look like word excel John 2 0 Mary 1 1 Ann 0 2 As I say, my list of usernames is over 100 and the total rows is over 20000 so manual counting (even sorting my name) would be a chore. I also tried the below formulae but it doesn't seem to work (found reference from the web and adapted it to suit my needs but may have made a mistke?) =SUM(IF($H2:$H4="John", IF($I2:$I4="word",1,0),0)) Apologies for the long post, if any one can point me in the right direction I'd really appreciate it. regards Gerry |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif multiple conditions
Hi Gerry - There is a nifty function called SUMPRODUCT which can be used to
check any number of conditions and count the number of times all of them are met. It's not exactly intuitive, but once you get used to it you'll probably get a lot out of it. In your example, the list of people and tools they used is in SheetA, starting at A1. In SheetB set up a table with names down the left and tools across the top. If you started that table in A1 then the first cell you need to populate is B2, correct? In B2 type (or paste): =SUMPRODUCT(--(SheetA!$A$1:$A$6=$A2),--(SheetA!$B$1:$B$6=B$1)) Now copy that formula to all the other empty cells in the table. Of course, if your sheet names or cell locations are different, you'll to adjust the formula. You probably want to know how it works, so here is some explanation. Each factor in SUMPRODUCT is a set of TRUE/FALSE evaluations. So the first factor tests to see if the names in column A of SheetA are equal to the name to the far left of the formula in SheetB. For example, the formula I provided above tests all the names to see which ones are equal to "John". The output from that par of the function is "True,False,False,True,False,False". The "--" in front of the factor converts True to "1" and "False" to "0". The second factor does the same thing, but looks in column B of SheetA for the tools. Its findings are also converted to 1's and 0's. SUMPRODUCT multiplies all the pairs together (1X1,0X0,0X0,1X1,0X1,0X0) and then sums all those products. In case you're not familiar, the $ in front of row or column references "anchor" those letters/numbers so that when you copy a formula to another location they don't adjust. In the formula I provided above, you want some or the references to adjust and others not to. If you were to simply type the adjusted formula in each cell in the table, you could omit the $. Hope that helps. Write back if stuck. "gerryR" wrote: Hi All I'm trying to setup a formula which counts the amount of times 2 particular conditions are true. From what I've read, =countif can only be used for single conditions. I've tried some other options (see below) but all to no avail. Below is an eg of what I'm trying to do. Basically, I have a worksheet (A) with several coulms and 20000 rows. John word Mary excel Ann excel John word Mary word Ann excel I then have another worksheet (B) with just a list of user names John Mary Ann What I want to do is count the amount of times each user has used each app. So the final worksheet (B) would look like word excel John 2 0 Mary 1 1 Ann 0 2 As I say, my list of usernames is over 100 and the total rows is over 20000 so manual counting (even sorting my name) would be a chore. I also tried the below formulae but it doesn't seem to work (found reference from the web and adapted it to suit my needs but may have made a mistke?) =SUM(IF($H2:$H4="John", IF($I2:$I4="word",1,0),0)) Apologies for the long post, if any one can point me in the right direction I'd really appreciate it. regards Gerry |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif multiple conditions
Thanks Andy, I used a pivot table in this instance but I'll definetly save
that one for later!. rgds gerry "andy62" wrote in message ... Hi Gerry - There is a nifty function called SUMPRODUCT which can be used to check any number of conditions and count the number of times all of them are met. It's not exactly intuitive, but once you get used to it you'll probably get a lot out of it. In your example, the list of people and tools they used is in SheetA, starting at A1. In SheetB set up a table with names down the left and tools across the top. If you started that table in A1 then the first cell you need to populate is B2, correct? In B2 type (or paste): =SUMPRODUCT(--(SheetA!$A$1:$A$6=$A2),--(SheetA!$B$1:$B$6=B$1)) Now copy that formula to all the other empty cells in the table. Of course, if your sheet names or cell locations are different, you'll to adjust the formula. You probably want to know how it works, so here is some explanation. Each factor in SUMPRODUCT is a set of TRUE/FALSE evaluations. So the first factor tests to see if the names in column A of SheetA are equal to the name to the far left of the formula in SheetB. For example, the formula I provided above tests all the names to see which ones are equal to "John". The output from that par of the function is "True,False,False,True,False,False". The "--" in front of the factor converts True to "1" and "False" to "0". The second factor does the same thing, but looks in column B of SheetA for the tools. Its findings are also converted to 1's and 0's. SUMPRODUCT multiplies all the pairs together (1X1,0X0,0X0,1X1,0X1,0X0) and then sums all those products. In case you're not familiar, the $ in front of row or column references "anchor" those letters/numbers so that when you copy a formula to another location they don't adjust. In the formula I provided above, you want some or the references to adjust and others not to. If you were to simply type the adjusted formula in each cell in the table, you could omit the $. Hope that helps. Write back if stuck. "gerryR" wrote: Hi All I'm trying to setup a formula which counts the amount of times 2 particular conditions are true. From what I've read, =countif can only be used for single conditions. I've tried some other options (see below) but all to no avail. Below is an eg of what I'm trying to do. Basically, I have a worksheet (A) with several coulms and 20000 rows. John word Mary excel Ann excel John word Mary word Ann excel I then have another worksheet (B) with just a list of user names John Mary Ann What I want to do is count the amount of times each user has used each app. So the final worksheet (B) would look like word excel John 2 0 Mary 1 1 Ann 0 2 As I say, my list of usernames is over 100 and the total rows is over 20000 so manual counting (even sorting my name) would be a chore. I also tried the below formulae but it doesn't seem to work (found reference from the web and adapted it to suit my needs but may have made a mistke?) =SUM(IF($H2:$H4="John", IF($I2:$I4="word",1,0),0)) Apologies for the long post, if any one can point me in the right direction I'd really appreciate it. regards Gerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIf multiple conditions | Excel Worksheet Functions | |||
countif-multiple conditions | Excel Discussion (Misc queries) | |||
Countif with Multiple Not Conditions | Excel Discussion (Misc queries) | |||
COUNTIF with multiple conditions | Excel Discussion (Misc queries) | |||
Multiple conditions on a countif | Excel Worksheet Functions |