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]

Let me rephrase to make it more clear:

I'm trying to create a SUMPRODUCT and an IF function. Here's the SUMPRODUCT function that works great:

=SUMPRODUCT((LEN(AS2:AS212)-LEN(SUBSTITUTE(AS2:AS212,"Yes","")))/LEN("Yes"))

Then I need to add to that formula to count 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>

So if I had a totals sheet, it would look like this. I just can't get Excel to do this:

Vermilion High School		2
Memorial		2
South Western		4

<tbody>
</tbody>

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

 

[Aladin Akyurek]

Your output does not seem to consider vermilion at all, regarding the memorial line.

Are you just wanting to count the occurrences of yes per record?

 

[FDibbins]

To keep this simple, I would probably just use a helper column, copy this down then sum the helper...
=IF(ISNUMBER(SEARCH("vermilion",B2:B5)),(LEN(C2)-LEN(SUBSTITUTE(C2,"Yes","")))/3,"")

 

[jme_4477]

Thank you for helping. Yes, I don't know how to take "vermilion" into account. Right now, it totals all of the "Yes," but I need it total all of the "Yes's" in the AS column of the B column has "vermilion" listed. Does that make sense?

 

[FDibbins]

the ranges in my suggestion would need to be adjusted to match what you have, but have you tried my suggested helper column?

 

[jme_4477]

Hi Ford,

Thank you for trying to help. That didn't work though. It might have been the LEN function though. What does the C2 stand for?

 

[FDibbins]

It worked for me (else I wouldn't have suggested it lol)...

	B​	C​	D​
1​	Column B	Column AS	Helper
2​	Vermilion High School	Yes, Yes,	2​
3​	Memorial	Yes, No, Yes
4​	South Western	Yes, Yes, Yes, Yes, No

D2=IF(ISNUMBER(SEARCH("vermilion",B2:B5)),(LEN(C2)-LEN(SUBSTITUTE(C2,"Yes","")))/3,"")
copied down

 

[Aladin Akyurek]

Thank you for helping. Yes, I don't know how to take "vermilion" into account. Right now, it totals all of the "Yes," but I need it total all of the "Yes's" in the AS column of the B column has "vermilion" listed. Does that make sense?

It would make more sense if you would not have included irrelevant counts...

Row\Col	B​	AS​	AT​
1​
2​	Vermilion High School	Yes, Yes,	2​
3​	Memorial	Yes, No, Yes
4​	South Western	Yes, Yes, Yes, Yes, No
5​

<tbody>
</tbody>

In AT2 enter and copy down:

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

 

[jme_4477]

Thank you both so much for you help. I am TRULY so appreciative. Let me give you a bigger picture though. I don't think I explained it well. I have a spreadsheet that has lots of "vermilon" in different cells in Column B:

A	B	C
PCS	123	Yes, No, Yes, Yes
Vermilion	836	Yes
Christian Academy	178	Yes, Yes
Great Lakes	815	Yes, No
Great Lakes	176	No, No
Vermilion	179	Yes, No
Christian Academy	110	Yes, Yes, Yes
TCAPS	177	Yes
Vermilion	175	Yes, Yes
Christian Academy	178	Yes
Vermilion	139	No
Vermilion	174	No, Yes
Downington	176	Yes

<tbody>
</tbody>

I need to be able to total all the "Yes's" from C (even when there is more than one in a cell), but only IF column A has "Vermilion" in it (and many cells do), then I need all of the Yes's totaled. In the above chart, I need Excel to be able to do this:

TOTALS:

A	B
PCS	3
Vermilion	5
Christian Academy	6
Great Lakes	1
Christian Academy	6
TCAPS	1

<tbody>
</tbody>