| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how do i count how many times x in column a = 1 or 2 in column b
In column A, I have the numbers 1 - 6 recurring many times. In column B, I
have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column b
=SUM((A1:A100=1)*(B1:B100=5)) hit ctrl+shift+enter
=SUM((A1:A100=2)*(B1:B100=5)) hit ctrl+shift+enter "Tony the Bajan" skrev: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column b
=SUMPRODUCT(--(A1:A100)=1),--(B1:B100=5))
"Tony the Bajan" wrote: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column b
Try sumproduct.
sumproduct((a1:a100=1)*(b1:b100=$5)) Tony the Bajan wrote: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column
Hi Excelent,
Thanks for your time and suggestion but neither works. It only counts cell a1 and b1 and gives the answer 1 even when I change all the cells in col A to be 1 and all the cells in col B to be 5. Cheers "excelent" wrote: =SUM((A1:A100=1)*(B1:B100=5)) hit ctrl+shift+enter =SUM((A1:A100=2)*(B1:B100=5)) hit ctrl+shift+enter "Tony the Bajan" skrev: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column
Hi Toppers,
This returns a value of 0 even when i change all the cells in col A to 1 and all of b to 5. Thanks for the suggestion. Tony "Toppers" wrote: =SUMPRODUCT(--(A1:A100)=1),--(B1:B100=5)) "Tony the Bajan" wrote: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column
Thank you very much Willwonka, problem solved.
Have a good day, year and life. Tony the bajan "willwonka" wrote: Try sumproduct. sumproduct((a1:a100=1)*(b1:b100=$5)) Tony the Bajan wrote: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column
Sorry ... typo ...
=SUMPRODUCT(--(A1:A100=1),--(B1:B100=5)) "Tony the Bajan" wrote: Hi Toppers, This returns a value of 0 even when i change all the cells in col A to 1 and all of b to 5. Thanks for the suggestion. Tony "Toppers" wrote: =SUMPRODUCT(--(A1:A100)=1),--(B1:B100=5)) "Tony the Bajan" wrote: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
how do i count how many times x in column a = 1 or 2 in column
well then i think u forgot to hit CTRL+SHIFT+ENTER after pasting the formula
"Tony the Bajan" skrev: Hi Excelent, Thanks for your time and suggestion but neither works. It only counts cell a1 and b1 and gives the answer 1 even when I change all the cells in col A to be 1 and all the cells in col B to be 5. Cheers "excelent" wrote: =SUM((A1:A100=1)*(B1:B100=5)) hit ctrl+shift+enter =SUM((A1:A100=2)*(B1:B100=5)) hit ctrl+shift+enter "Tony the Bajan" skrev: In column A, I have the numbers 1 - 6 recurring many times. In column B, I have different prices for 1-6 eg, $5, $6, $7 and there are multiple occurences where 1 =$5, $6 etc What function do I type into a cell in column C to count how many times 1 = $5, 1 = $6, 2 = $5, 2 = $6 etc. I have tried =countif(a1:a100=1,b1:b100=$5) no good. =if(and(a1:a100=1,b1:b100=$5) no good either. Seems so simple. Help Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
| How do I count how many times a code appears in a column? | Excel Worksheet Functions | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
| How do I count how many times x appears in a column? | Excel Worksheet Functions | |||
Linear Mode
