Help with Countif Formula

[znxm0i]

Hi all,

I need to be able to count the number of "yes" entries in a cell based on whether another cell contains a specific text. Here's my scenario:

I have a tab called raw data that contains columns called: ID#, Title, Internal, Name, Location, Unit
I want to count the number of "yes" entries in the Internal column that correspond with a particular Unit
For example, if Unit = "IOS" then find all "yes" entries in the Internal column for only "IOS" and give me the total count

I've been trying to use the following formula but it doesn't work, instead it's counting both the specific instances in the Unit and Internal column and summing the count.

Thank you.

 

[znxm0i]

Never mind all,

I figured it out by using the below formula:

=COUNTIFS($T$4:$T$136,"=IOS",$O$4:$O$136,"=Yes")

I wasn't putting the equal signs in front of the criteria

 

[Joe4]

You don't actually need the equal signs at all, in this case, i.e.

[COLOR=#333333]=COUNTIFS($T$4:$T$136,"IOS",$O$4:$O$136,"Yes")[/COLOR]

 

[znxm0i]

You don't actually need the equal signs at all, in this case, i.e.

[COLOR=#333333]=COUNTIFS($T$4:$T$136,"IOS",$O$4:$O$136,"Yes")[/COLOR]

Thank you

I have another question related to this issue. I have the below formula in my spreadsheet but what I'd want to do is tell it to display the number zero for those cells that don't have a value in rows T4:T136. I think that I would use an IF statement but I haven't been able to get it to work because I'm not really sure where to place it. Can you help?

=AVERAGEIF('Raw Data'!$T$4:$T$136, "Commercial", 'Raw Data'!$U$4:$U$136)

 

[Joe4]

Try this:

=[COLOR=#0000ff]IF(COUNTA('Raw Data'!$T$4:$T$136)=0,0,[/COLOR][COLOR=#333333]AVERAGEIF('Raw Data'!$T$4:$T$136, "Commercial", 'Raw Data'!$U$4:$U$136)[/COLOR][COLOR=#0000ff])[/COLOR]

Or, if you really want to check not just for any text, but rather "Commercial", then use:

=[COLOR=#0000ff]IF(COUNTIF('Raw Data'!$T$4:$T$136,"Commercial")=0,0,[/COLOR][COLOR=#333333]AVERAGEIF('Raw Data'!$T$4:$T$136, "Commercial", 'Raw Data'!$U$4:$U$136)[/COLOR][COLOR=#0000ff])[/COLOR]