Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Multiple Values
Hi,
I have two columns of fixed, equal, size (A1:A100 and B1:B100). Both columns are completely populated by formulae returning values from other parts of the workbook, These values are text not numeric. Where there is no value, the cell is empty (other than the formula, of course). It is not possible for A to be empty and B not to be emptty and vice versa. The whole thing looks something like: A B 1 Apple Pie 2 3 Pear Juice 4 Apple Pie .. .. 100 Apple Juice What I want to do is count the number of each variation and output it in a table: Pie Juice Apple 2 1 Pear 0 1 This seems to requre ANDing two COUNTIF statements, along the lines of: =AND(countif(A1:A100,"apple"),countif(B1:B100,"Pie ") With each cell of the table having a custom variation of this formula. The AND statement is returning a TRUE or FALSE, so how do I count the number of Apple Pies, Apple Juices, Pear Pies etc? TIA Dave |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Multiple Values
Try this:
With your posted set up... F1: Pie G1: Juice E2: Apple E3: Pear F2: =SUMPRODUCT(--($A$2:$A$100=$E2),--($B$2:$B$100=F$1)) Copy F2 across and down through G3 Alternatively, you could just use a pivot table which would automatically create the structure you're looking for Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Risky Dave" wrote in message ... Hi, I have two columns of fixed, equal, size (A1:A100 and B1:B100). Both columns are completely populated by formulae returning values from other parts of the workbook, These values are text not numeric. Where there is no value, the cell is empty (other than the formula, of course). It is not possible for A to be empty and B not to be emptty and vice versa. The whole thing looks something like: A B 1 Apple Pie 2 3 Pear Juice 4 Apple Pie . . 100 Apple Juice What I want to do is count the number of each variation and output it in a table: Pie Juice Apple 2 1 Pear 0 1 This seems to requre ANDing two COUNTIF statements, along the lines of: =AND(countif(A1:A100,"apple"),countif(B1:B100,"Pie ") With each cell of the table having a custom variation of this formula. The AND statement is returning a TRUE or FALSE, so how do I count the number of Apple Pies, Apple Juices, Pear Pies etc? TIA Dave |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Multiple Values
Perfik!
Many thanks "Ron Coderre" wrote: Try this: With your posted set up... F1: Pie G1: Juice E2: Apple E3: Pear F2: =SUMPRODUCT(--($A$2:$A$100=$E2),--($B$2:$B$100=F$1)) Copy F2 across and down through G3 Alternatively, you could just use a pivot table which would automatically create the structure you're looking for Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Risky Dave" wrote in message ... Hi, I have two columns of fixed, equal, size (A1:A100 and B1:B100). Both columns are completely populated by formulae returning values from other parts of the workbook, These values are text not numeric. Where there is no value, the cell is empty (other than the formula, of course). It is not possible for A to be empty and B not to be emptty and vice versa. The whole thing looks something like: A B 1 Apple Pie 2 3 Pear Juice 4 Apple Pie . . 100 Apple Juice What I want to do is count the number of each variation and output it in a table: Pie Juice Apple 2 1 Pear 0 1 This seems to requre ANDing two COUNTIF statements, along the lines of: =AND(countif(A1:A100,"apple"),countif(B1:B100,"Pie ") With each cell of the table having a custom variation of this formula. The AND statement is returning a TRUE or FALSE, so how do I count the number of Apple Pies, Apple Juices, Pear Pies etc? TIA Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF Multiple Values
You're very welcome....I'm glad I could help.
Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Risky Dave" wrote in message ... Perfik! Many thanks "Ron Coderre" wrote: Try this: With your posted set up... F1: Pie G1: Juice E2: Apple E3: Pear F2: =SUMPRODUCT(--($A$2:$A$100=$E2),--($B$2:$B$100=F$1)) Copy F2 across and down through G3 Alternatively, you could just use a pivot table which would automatically create the structure you're looking for Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Risky Dave" wrote in message ... Hi, I have two columns of fixed, equal, size (A1:A100 and B1:B100). Both columns are completely populated by formulae returning values from other parts of the workbook, These values are text not numeric. Where there is no value, the cell is empty (other than the formula, of course). It is not possible for A to be empty and B not to be emptty and vice versa. The whole thing looks something like: A B 1 Apple Pie 2 3 Pear Juice 4 Apple Pie . . 100 Apple Juice What I want to do is count the number of each variation and output it in a table: Pie Juice Apple 2 1 Pear 0 1 This seems to requre ANDing two COUNTIF statements, along the lines of: =AND(countif(A1:A100,"apple"),countif(B1:B100,"Pie ") With each cell of the table having a custom variation of this formula. The AND statement is returning a TRUE or FALSE, so how do I count the number of Apple Pies, Apple Juices, Pear Pies etc? TIA Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif with multiple text values in cell | Excel Discussion (Misc queries) | |||
Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
Adding multiple values in one column based on multiple values of the same value (text) in another column | Excel Discussion (Misc queries) | |||
Using COUNTIF to check values in multiple columns | Excel Worksheet Functions | |||
Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions |