# Need Help with Countif

#### pkohli

##### Board Regular
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..

### 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
perhaps
Code:
``=sumproduct(--(A:A="A"),--(B:B<>0))``

#### pkohli

##### Board Regular
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
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
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
=sumproduct(--(a1:a100="Ben"),--(b1:b100="UK"),--(c1:c100="H"))

#### hatman

##### Well-known Member
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 forgot

#### pkohli

##### Board Regular
What if I want to use whole number also?? As mentioned in the previous post??

#### barry houdini

##### MrExcel MVP
What exactly are the criteria? column B needs to be any number?

Perhaps try

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

Replies
7
Views
296
Replies
3
Views
323
Replies
4
Views
334
Replies
7
Views
384
Replies
5
Views
387

1,171,584
Messages
5,876,307
Members
433,193
Latest member

### 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.

### Which adblocker are you using?

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

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