Help with Counting Records Using Multiple Criteria

[MartinS]

Can't think how to do this this morning!
I have some data (wedding guests) in a spreadsheet, and we are working out costings for food/travel etc, so have multiple columns. The first column is name(s), i.e.
Fred & Barbara
Jon & Rob
Sarah
Betty & Barnie
Gabbi
I wanted to count the number of guests, so used the following formula:

=(COUNTA(B4:B9)-COUNTIF(B4:B9,"*&*"))+COUNTIF(B4:B9,D11)*2

So, for the selection above, returns 8.
What I also need is to count the number of guests with 'Yes' in another column, and that's where I'm struggling!
Fred & Barbara Yes
Jon & Rob No
Sarah Yes
Betty & Barnie Yes
Gabbi No
So my formula wants to know how many guests have said yes - in the example above, it should return 5.
The formula needs to count each row that has 'Yes' in column 2, and then for each 'Yes' row, the number of guests.
Hope that makes sense?
Thanks
Martin

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data.

Would you ever have entries like "Tom & Bob & Fred" or "Tom, Bob & Fred"?

 

[MartinS]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data.

Would you ever have entries like "Tom & Bob & Fred" or "Tom, Bob & Fred"?

Hi
Apologies - I have updated my details, and for info, we are running office 365.
And to answer your question, no, as it's a list of wedding guests, so it's either singles or couples.
I did think I could simply add a column that has the number of guests per row, then use a SUMPRODUCT formula, such as:

=SUMPRODUCT(--(B4:B9="Yes"),C4:C9)

Where column C has the formula:

=1+IF(ISERROR(FIND("&",A4)),0,1)

But am hopeful I don't need the extra column C...
Thanks
Martin

 

[Peter_SSs]

Thanks for updating your profile.

What about this?

21 09 16.xlsm
	B	C	D	E
3				Yes
4	Fred & Barbara	Yes		5
5	Jon & Rob	No
6	Sarah	Yes
7	Betty & Barnie	Yes
8	Gabbi	No
Count

Cell Formulas
Range	Formula
E4	E4	=LET(t,CONCAT(IF(C4:C8="Yes","&"&B4:B8,"")),LEN(t)-LEN(SUBSTITUTE(t,"&","")))

 

[Peter_SSs]

A better way

21 09 16.xlsm
	B	C	D	E
3				Yes
4	Fred & Barbara	Yes		5
5	Jon & Rob	No
6	Sarah	Yes
7	Betty & Barnie	Yes
8	Gabbi	No
Count

Cell Formulas
Range	Formula
E4	E4	=COUNTIF(C4:C8,"Yes")+COUNTIFS(C4:C8,"Yes",B4:B8,"*&*")

 

[MartinS]

Thanks @Peter_SSs
Oddly, your first formula returns #NAME!, but the second one works perfectly and makes sense!
Thanks again!
Martin

 

[Peter_SSs]

Oddly, your first formula returns #NAME!,

Sounds like you might not have the LET function yet. Perhaps you have some updates to do?

In any case the second formula is better anyway so all is good.

 

[MartinS]

Sounds like you might not have the LET function yet. Perhaps you have some updates to do?

In any case the second formula is better anyway so all is good.

Our updates are controlled, so I have whatever has been deployed. According to Excel, I have Excel for Office 365 MSO (16.0.12527.21798) 32-bit
But agreed, the second one works perfectly!
Thanks again

 

[Peter_SSs]

Our updates are controlled, so I have whatever has been deployed.

LET is a very handy function so perhaps you could start to agitate a little for some more "deployment"? ?