Combining SUMPRODUCT and COUNTIF

[jme_4477]

I'm trying to combine these two formulas:

=SUMPRODUCT((LEN(AS2:AS212)-LEN(SUBSTITUTE(AS2:AS212,"Yes","")))/LEN("Yes"))
=countif(b2:b212="*vermilion*")

Basically, the first formula counts the number of "Yes" in one column, but I need it to only count the "Yes" if another column has "vermilion" in it.

Here's what part of my spreadsheet:

Column B		Column AS
Vermilion High School		Yes, Yes,
Memorial		Yes, No, Yes
South Western		Yes, Yes, Yes, Yes, No

<tbody>
</tbody>

Does anyone know how to do this? I've been working on this for 8 hours!!!

 

[jme_4477]

I tried the code and added in the cell ranges, but it didn't work I get a formula error, and it won't even let me click Enter.

=IF(ISNUMBER(SEARCH("vermilion",B2:B212)),SUMPRODUCT(LEN(AS2:AS212)-LEN(SUBSTITUTE(UPPER(AS2):AS212,"YES","")))/LEN("yes"),"")

 

[jme_4477]

I coped it in wrong...Here's what I have entered:
=IF(ISNUMBER(SEARCH("vermilion",B2:B212)),SUMPRODUCT(LEN(AS2:AS212)-LEN(SUBSTITUTE(UPPER(AS2:AS212,"Yes","")))/LEN("Yes"),"")

It doesn't work

 

[Aladin Akyurek]

You are impatient too...

DATA

Row\Col	A​	B​	C​
1​	Field-1	Field-2	Field-3
2​	PCS	123	Yes, No, Yes, Yes
3​	Vermilion	836	Yes
4​	Christian Academy	178	Yes, Yes
5​	Great Lakes	815	Yes, No
6​	Great Lakes	176	No, No
7​	Vermilion	179	Yes, No
8​	Christian Academy	110	Yes, Yes, Yes
9​	TCAPS	177	Yes
10​	Vermilion	175	Yes, Yes
11​	Christian Academy	178	Yes
12​	Vermilion	139	No
13​	Vermilion	174	No, Yes
14​	Downington	176	Yes

<tbody>
</tbody>

TOTALS

Row\Col	A​	B​
1​	Field-1	Count
2​	PCS	3​
3​	Vermilion	5​
4​	Christian Academy	6​
5​	Great Lakes	1​
6​	TCAPS	1​
7​	Downington	1​

<tbody>
</tbody>

<strike></strike>
In B2 control+shift+enter, not just enter, and copy down:

=SUM(IF(DATA!$A$2:$A$14=$A2,
    (LEN(DATA!$C$2:$C$14)-LEN(SUBSTITUTE(UPPER(DATA!$C$2:$C$14),"YES","")))/LEN("yes")))

<strike></strike>

 

[jme_4477]

Oh, I can't tell you how appreciative I am, and I apologize for my confusion. What do you mean, though, by pressing control+shift+enter when I copy? How do I do that? I'm trying to enter this formula beneath the table. It's in cell AS214.

 

[jme_4477]

Actually, here's what I typed out:
=SUM(IF(DATA!$B$2:$B$212=$B2,(LEN(DATA!$AS$2:$AS$212)-LEN(SUBSTITUTE(UPPER(DATA!$AS$2:$AS$212),"Yes","")))/LEN("Yes")))

In my spreadsheet, the programs "vermilion" is in Column B, and the "Yes" are in Column AS. And the rows go from 2 to 212.

 

[jme_4477]

It looks like this:

ow\Col	B​	​	AS​
1​	Field-1	Field-2	Field-3
2​	PCS	123	Yes, No, Yes, Yes
3​	Vermilion	836	Yes
4​	Christian Academy	178	Yes, Yes
5​	Great Lakes	815	Yes, No
6​	Great Lakes	176	No, No
7​	Vermilion	179	Yes, No
8​	Christian Academy	110	Yes, Yes, Yes
9​	TCAPS	177	Yes
10​	Vermilion	175	Yes, Yes
11​	Christian Academy	178	Yes
12​	Vermilion	139	No
13​	Vermilion	174	No, Yes
212​	Downington	176	Yes

<tbody>
</tbody>

 

[Aladin Akyurek]

Oh, I can't tell you how appreciative I am, and I apologize for my confusion. What do you mean, though, by pressing control+shift+enter when I copy? How do I do that? I'm trying to enter this formula beneath the table. It's in cell AS214.

First we had B and AS, then A, B, C, now back to AS...

Control+shift+enter: Press down the control and the shift keys at the same time, while you hit the enter key. İf done properly, Excel itself puts a pair of { and } around the formula...

 

[jme_4477]

Thank you again. Whether or not I press the control + shift + enter or not though, a file browsing window pops open with the name "Update Values: DATA" in it. I don't know what this is. I think it might have to do with the "data!" that is entered in the formula a few times. What is that part of the command for?

 

[Aladin Akyurek]

Thank you again. Whether or not I press the control + shift + enter or not though, a file browsing window pops open with the name "Update Values: DATA" in it. I don't know what this is. I think it might have to do with the "data!" that is entered in the formula a few times. What is that part of the command for?

DATA refers to the name of the sheet where data is located.

If you closely inspect my reply, you'll see the source is displayed under the caption DATA, the results under the caption TOTALS (this one implied by yourself).

 

[jme_4477]

Oh, I think we are getting closer. Thank you again for your patience. The name of the sheet with the data is called Sheet2. She on a new sheet, I have typed in this formula:

=SUM(IF(Sheet2!$B$2:$B$212=$B2,(LEN(Sheet2!$AS$2:$AS$212)-LEN(SUBSTITUTE(UPPER(Sheet2!$AS$2:$AS$212),"*Yes*","")))/LEN("*Yes*")))

I don't get any errors, but unfortunately, it doesn't give me the total either. Any ideas?