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

T

Tony the Bajan

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
 
E

excelent

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

Tony the Bajan

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
 
T

Tony the Bajan

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
 
T

Tony the Bajan

Thank you very much Willwonka, problem solved.

Have a good day, year and life.

Tony the bajan
 
E

excelent

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

"Tony the Bajan" skrev:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top