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 did that. It still didn't work.

Whenever I change the range to go to 212, even when I reconfirm the formula with control+shift+enter, the total changes to #N/A.

 

[jme_4477]

Whenever I change the range to go to 212, even when I reconfirm the formula with control+shift+enter, the total changes to #N/A.

Something interesting and confusing.....When I change the range slightly, it works:

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

It goes up to B30 and AS30 just fine. When I try and go higher, like to B40 and AS40, though, that's when I get the #N/A error in the cell.

Any ideas?

 

[Aladin Akyurek]

Whenever I change the range to go to 212, even when I reconfirm the formula with control+shift+enter, the total changes to #N/A.

Something interesting and confusing.....When I change the range slightly, it works:

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

It goes up to B30 and AS30 just fine. When I try and go higher, like to B40 and AS40, though, that's when I get the #N/A error in the cell.

Any ideas?

No problem over here. See the link. Do you have #N/A in B or AS as result of some formula in these columns? By the way, the ranges must be equally sized...

 

[jme_4477]

No problem over here. See the link. Do you have #N/A in B or AS as result of some formula in these columns? By the way, the ranges must be equally sized...

That was it!!! Some of my cells in B had N/A in them. YOU ARE AMAZING!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Thank you!!

 

[Aladin Akyurek]

That was it!!! Some of my cells in B had N/A in them. YOU ARE AMAZING!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! Thank you!!

Glad to see it's sorted out.

 

[jme_4477]

I have one final question for you. On my same Totals sheet, I'm trying to count each time "vermilion" appears in B2 to B212. I know I can do it like this: =COUNTIF(Sheet2!B2:B212,"vermilion")

But is there a way to link it back to the A2 cell in my Totals sheet (because A2 in my Totals sheet already has "Vermilion" in the cell)? I think it would be something like this, but it's not working: =COUNTIF(Sheet2!$B$2:$B$212=$A2)

 

[Aladin Akyurek]

I have one final question for you. On my same Totals sheet, I'm trying to count each time "vermilion" appears in B2 to B212. I know I can do it like this: =COUNTIF(Sheet2!B2:B212,"vermilion")

But is there a way to link it back to the A2 cell in my Totals sheet (because A2 in my Totals sheet already has "Vermilion" in the cell)? I think it would be something like this, but it's not working: =COUNTIF(Sheet2!$B$2:$B$212=$A2)

Again that Vermilion?

=COUNTIFS(Sheet2!$B$2:$B$212,"*"&$A2&"*")

 

[jme_4477]

Again that Vermilion?

=COUNTIFS(Sheet2!$B$2:$B$212,"*"&$A2&"*")

You are TRULY amazing! Thank you again VERY, VERY much!!!!!!!

 

[Aladin Akyurek]

You are TRULY amazing! Thank you again VERY, VERY much!!!!!!!

Glad to help.