Counting x in Column A if Column B equals to y

[mugies]

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

[popipipo]

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

[mugies]

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.

[popipipo]

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

[SDCh]

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

[Kevin UK]

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.

[SDCh]

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

[popipipo]

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

[Tony Valko]

You can also use 'countif'...

COUNTIF won't work in this application as there are multiple separate conditions.

[mugies]

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.