# 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

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

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

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.

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

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

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

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.

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.

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

You can also use 'countif' or use a pivot table.

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1