how do i count how many times x in column a = 1 or 2 in column b

[Guest]

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

 

[Guest]

=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:

 

[Guest]

=SUMPRODUCT(--(A1:A100)=1),--(B1:B100=5))

 

[willwonka]

Try sumproduct.

sumproduct((a1:a100=1)*(b1:b100=$5))

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank you very much Willwonka, problem solved.

Have a good day, year and life.

Tony the bajan

 

[Guest]

Sorry ... typo ...

=SUMPRODUCT(--(A1:A100=1),--(B1:B100=5))

 

[Guest]

well then i think u forgot to hit CTRL+SHIFT+ENTER after pasting the formula

"Tony the Bajan" skrev: