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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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
=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,171,584
Messages
5,876,307
Members
433,193
Latest member
BruxoTrader

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