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

ParExcellence

New Member
Joined
Nov 30, 2016
Messages
2
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
Joined
Nov 22, 2016
Messages
63
Hi,

If your table looks like :

A101yes
A101no
A101yes
A102yes
A103no
A102yes
A103no

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

Then use sumproduct() instead of countif()

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

ParExcellence

New Member
Joined
Nov 30, 2016
Messages
2
Hi,

If your table looks like :

A101yes
A101no
A101yes
A102yes
A103no
A102yes
A103no

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

A101yesno
A101nono
A101yesno
A102yesyes
A103nono
A102yesyes
A103noyes

<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?
 
Upvote 0

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
Almost...but not quite what I need.


The table looks more like this:

A101yesno
A101nono
A101yesno
A102yesyes
A103nono
A102yesyes
A103noyes

<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
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")
 
Upvote 0

63falcondude

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
or =SUMPRODUCT((A2:A8="A103")*(B2:C8="no"))

The ranges are referencing to my previous post.
 
Upvote 0

MARZIOTULLIO

Well-known Member
Joined
Aug 22, 2015
Messages
767
A
B
C
D
1
A101yesno
3​
2
A101nono
3
A101yesno
4
A102yesyes
5
A103nono
6
A102yesyes
7
A103noyes

<tbody>
</tbody>


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

Forum statistics

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