# Trying to get a COUNTIF to work a certain way...

#### ParExcellence

##### New Member
Hi, everyone. I'm here because I have searched and searched for an answer to what I'm trying to do to no avail. I'll explain as best I can.

At work, I am trying to create a Summary sheet for some data we've been crunching, and my manager wants a table to display certain counts of certain data. The problem lies in how it should be counted.

Basically, there is a column with a record ID number in it, and for each code, I want to count how many instances of the word "yes"(it makes sense in context) appear in the row containing that record ID number. For an example, say there's three rows with ID numbers of A101, A102, and A103. In the Summary sheet, I want to put the count of how many times the word "yes" appears in the A102 row only in a cell in the Summary sheet.

I can't get it to work, and i wanted to hop on here and ask, is it possible to achieve that effect? Sorry if I'm unclear, I got rather rusty with Excel before I took this job. If I need to clarify, please just ask. And thank you all in advance for taking the time.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Anand Sharma

##### Board Regular
Hi,

If your table looks like :

 A101 yes A101 no A101 yes A102 yes A103 no A102 yes A103 no

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Then use sumproduct() instead of countif()

=SUMPRODUCT(--(H11:H20="A101"),--(I11:I20="yes"))

#### ParExcellence

##### New Member
Hi,

If your table looks like :

 A101 yes A101 no A101 yes A102 yes A103 no A102 yes A103 no

<colgroup><col span="2"></colgroup><tbody>
</tbody>

Then use sumproduct() instead of countif()

=SUMPRODUCT(--(H11:H20="A101"),--(I11:I20="yes"))

Almost...but not quite what I need.

The table looks more like this:

 A101 yes no A101 no no A101 yes no A102 yes yes A103 no no A102 yes yes A103 no yes

<colgroup><col span="3"></colgroup><tbody>
</tbody>

As an example, I want to count every cell that contains "no" in every row where the code is A103, which is a count of 3 in this instance. Is that possible with this formula? Or at all?

#### 63falcondude

##### Well-known Member
Almost...but not quite what I need.

The table looks more like this:

 A101 yes no A101 no no A101 yes no A102 yes yes A103 no no A102 yes yes A103 no yes

<tbody>
</tbody>

As an example, I want to count every cell that contains "no" in every row where the code is A103, which is a count of 3 in this instance. Is that possible with this formula? Or at all?

Excel 2010
ABCDE
1
2A101yesno3
3A101nono
4A101yesno
5A102yesyes
6A103nono
7A102yesyes
8A103noyes
Sheet1
Cell Formulas
RangeFormula
E2=COUNTIFS(A:A,"A103",B:B,"no")+COUNTIFS(A:A,"A103",C:C,"no")

#### 63falcondude

##### Well-known Member
or =SUMPRODUCT((A2:A8="A103")*(B2:C8="no"))

The ranges are referencing to my previous post.

#### MARZIOTULLIO

##### Well-known Member
 A​ B​ C​ D​ 1​ A101 yes no 3​ 2​ A101 no no 3​ A101 yes no 4​ A102 yes yes 5​ A103 no no 6​ A102 yes yes 7​ A103 no yes

<tbody>
</tbody>

d1=SUMPRODUCT((A1:A7=A5)*(B1:C7=C5))

Replies
3
Views
303
Replies
3
Views
488
Replies
1
Views
372
Replies
10
Views
3K
Replies
2
Views
140

1,191,688
Messages
5,988,079
Members
440,125
Latest member
vincentchu2369

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