# Counting x in Column A if Column B equals to y

1. ## Counting x in Column A if Column B equals to y

Dear all pro's,

I'm just a new here. Hope anyone can help. I tried to search everywhere in the internet but i could not find the exact answer i wanted to.

Cherrs  Register To Reply

2. ## Re: Counting x in Column A if Column B equals to y

=sumproduct((A1:A100="x")*(B1:B100="y"))  Register To Reply

3. ## Re: Counting x in Column A if Column B equals to y

doesnt work. Okay, here is my actual problem.

ColumnA ColumnB
1 P
1 L
2 P
3 L
2 L
1 P

I wanted the excel to count number "1" in column A if only the ColumnB =P. The answer should be =2
How is this possible and what formula to use? Please advice. Thanks.  Register To Reply

4. ## Re: Counting x in Column A if Column B equals to y

=sumproduct((A1:A100=1)*(B1:B100="p"))  Register To Reply

5. ## Re: Counting x in Column A if Column B equals to y

You can use this function: ``Please Login or Register  to view this content.``
Note : change the red one for value in column A, change the blue for value in column B  Register To Reply

6. ## Re: Counting x in Column A if Column B equals to y

Hi mugies

May be: =SUMPRODUCT(--(A1:A6=1),--(B1:B6="P")) extend ranges to suit.

@ SDCh The OP's profile is Excel 2003. the COUNTIFS is not available in that version.  Register To Reply

7. ## Re: Counting x in Column A if Column B equals to y

@Kevin UK : Yes, you right, sorry not see the excel version that OP's use.  Register To Reply

8. ## Re: Counting x in Column A if Column B equals to y

You can also use 'countif' or use a pivot table.  Register To Reply

9. ## Re: Counting x in Column A if Column B equals to y Originally Posted by popipipo You can also use 'countif'...
COUNTIF won't work in this application as there are multiple separate conditions.  Register To Reply

10. ## Re: Counting x in Column A if Column B equals to y

Hi all,
Got it. Thanks popipipo.

popipipo Re: Counting x in Column A if Column B equals to y

=sumproduct((A1:A100=1)*(B1:B100="p"))
This formula works for me. Now im trying if third condition works.  Register To Reply