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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 12:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com