Need Help with Countif

pkohli

Board Regular
Joined
Aug 2, 2005
Messages
71
Hi

I need a help with countif function. I am trying to write a formula :-
Book1
ABCD
1A10
2B
3A
4C2
5A3
6B4
7C67
8C8
9B9
10
11A2
12B2
13C3
Sheet1


The formula I am trying to write is if column A has "A" count the values for A in column B...if there is no value then it shld count it as zero..

Can someone please help.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
perhaps
Code:
=sumproduct(--(A:A="A"),--(B:B<>0))
 

pkohli

Board Regular
Joined
Aug 2, 2005
Messages
71
Not working...I need to count not sum..also could some one tell me how to use countif with 2 criteria i.e. -
Book2
ABCD
1NameAddressStatus
2BenNAH
3RickyNAH
4LeeUKM
5LeeUKM
6FoeAUL
7RickyAUL
8RickyUKH
9RickyUSL
10BenUKM
11BenUKH
12BenAUM
13BenAUL
Sheet1


I want to count if name is ben and address is UK, how many H are in status.
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
You can't use whole columns for SUMPRODUCT but it should work if you use

=SUMPRODUCT(--(A2:A100="ben"),--(B2:B100="uk"),--(C2:C100="h"))
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

=sumproduct(--(a1:a100="Ben"),--(b1:b100="UK"),--(c1:c100="H"))
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
You can't use whole columns for SUMPRODUCT but it should work if you use

=SUMPRODUCT(--(A2:A100="ben"),--(B2:B100="uk"),--(C2:C100="h"))

:oops: oops :oops: forgot
 

pkohli

Board Regular
Joined
Aug 2, 2005
Messages
71
What if I want to use whole number also?? As mentioned in the previous post??
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
What exactly are the criteria? column B needs to be any number?

Perhaps try

=SUMPRODUCT(--(A1:A100="a"),--ISNUMBER(B1:B100))
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top