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

[ParExcellence]

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.

 

[Anand Sharma]

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]

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]

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?

How about something like this?

Excel 2010
	A	B	C	D	E
1
2	A101	yes	no		3
3	A101	no	no
4	A101	yes	no
5	A102	yes	yes
6	A103	no	no
7	A102	yes	yes
8	A103	no	yes
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIFS(A:A,"A103",B:B,"no")+COUNTIFS(A:A,"A103",C:C,"no")

 

[63falcondude]

or =SUMPRODUCT((A2:A8="A103")*(B2:C8="no"))

The ranges are referencing to my previous post.

 

[MARZIOTULLIO]

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