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

 

[Aladin Akyurek]

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?

Now it's my turn to get impatient. Why do you modify Yes to *Yes*"?

 

[jme_4477]

I tried it without that as well, but it didnt' work:


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

 

[jme_4477]

The count always comes up as 0.

 

[jme_4477]

I think I might know part of the problem. This formula doesn't take "vermilion" from column B. I'm trying to count all of the "Yes" in AS if "vermilion" appears in B.

 

[Aladin Akyurek]

The count always comes up as 0.

Not here. By the way, I have the criteria in column A of TOTALS. You appear thus to change A2 to B2 in the formula!

See the workbook https://dl.dropboxusercontent.com/u...nditional in-string yes occurrence count.xlsx that implements the formula, which must be confirmed with control+shift+enter:

=SUM(IF(Sheet2!$B$2:$B$14=$A2,(LEN(Sheet2!$AS$2:$AS$14)-LEN(SUBSTITUTE(UPPER(Sheet2!$AS$2:$AS$14),"YES","")))/LEN("yes")))

which adjusted to column B and AS of Sheet2.

 

[Aladin Akyurek]

Geez. Three posts (#22, #23, #24) before you get an answer to the first one.

 

[jme_4477]

IT WORKED!!!!! THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!

One last question if I may though....I need to expand the section on Sheet2, to look for "vermilion" from B2 to B212. Then it will need to count the "Yes" from AS2 to AS212. Here's the formula that worked, but I'm not sure how to expand the section:

=SUM(IF(Sheet2!$B$2:$B$14=$A2,(LEN(Sheet2!$AS$2:$AS$14)-LEN(SUBSTITUTE(UPPER(Sheet2!$AS$2:$AS$14),"YES","")))/LEN("yes")))

 

[jme_4477]

I tried this to expand the section, but it didn't work????

=SUM(IF(Sheet2!$B$2:$B$212=$A2,(LEN(Sheet2!$AS$2:$AS$212)-LEN(SUBSTITUTE(UPPER(Sheet2!$AS$2:$AS$212),"YES","")))/LEN("yes")))

 

[Aladin Akyurek]

IT WORKED!!!!! THANK YOU, THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!

One last question if I may though....I need to expand the section on Sheet2, to look for "vermilion" from B2 to B212. Then it will need to count the "Yes" from AS2 to AS212. Here's the formula that worked, but I'm not sure how to expand the section:

=SUM(IF(Sheet2!$B$2:$B$14=$A2,(LEN(Sheet2!$AS$2:$AS$14)-LEN(SUBSTITUTE(UPPER(Sheet2!$AS$2:$AS$14),"YES","")))/LEN("yes")))

I tried this to expand the section, but it didn't work????

=SUM(IF(Sheet2!$B$2:$B$212=$A2,(LEN(Sheet2!$AS$2:$AS$212)-LEN(SUBSTITUTE(UPPER(Sheet2!$AS$2:$AS$212),"YES","")))/LEN("yes")))

Why don't you wait for an answer before making another post? That asked:

You need to reconfirm the formula with control+shift+enter after you modify it.

 

[jme_4477]

I did that. It still didn't work.