This should be simple but I can't figure it out. I have created a table to
tabulate the number of guests to a party. Some are adults, some are
children, some are teens. Some are invited to only the morning event, some
to both morning and evening. Columns are "age category" (adult, child, teen
are entries), "coming to morning" (yes, no are entries), "coming to evening"
(yes, no are entries). Some have responded, many have not yet. All are text
entries. How do I create formulas to sum the adults attending morning,
adults attending evening and the same for children and teens? I've tried
using the IF, COUNT and COUNTIF functions, but none seem to work for text
data in more than one column. Thanks

Column B = "Adult"
Column C = "Child"
Column D = "Tean"
Column E = "Morning"
Column F = "Evening"

Count of Adults coming to Morning:

=SUMPRODUCT((B2:B100="Yes")*(E2:E100="Yes"))

Count of Tean comin to both:

=SUMPRODUCT((D2:D100="Yes")*(E2:E100="Yes")*(F2:F1 00="Yes"))

and so on

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"lippbj" wrote in message
...
This should be simple but I can't figure it out. I have created a table
to
tabulate the number of guests to a party. Some are adults, some are
children, some are teens. Some are invited to only the morning event,
some
to both morning and evening. Columns are "age category" (adult, child,
teen
are entries), "coming to morning" (yes, no are entries), "coming to
evening"
(yes, no are entries). Some have responded, many have not yet. All are
text
entries. How do I create formulas to sum the adults attending morning,
adults attending evening and the same for children and teens? I've tried
using the IF, COUNT and COUNTIF functions, but none seem to work for text
data in more than one column. Thanks

Thanks very much. You solved my problem.

"Sandy Mann" wrote:

Column B = "Adult"
Column C = "Child"
Column D = "Tean"
Column E = "Morning"
Column F = "Evening"

Count of Adults coming to Morning:

=SUMPRODUCT((B2:B100="Yes")*(E2:E100="Yes"))

Count of Tean comin to both:

=SUMPRODUCT((D2:D100="Yes")*(E2:E100="Yes")*(F2:F1 00="Yes"))

and so on

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"lippbj" wrote in message
...
This should be simple but I can't figure it out. I have created a table
to
tabulate the number of guests to a party. Some are adults, some are
children, some are teens. Some are invited to only the morning event,
some
to both morning and evening. Columns are "age category" (adult, child,
teen
are entries), "coming to morning" (yes, no are entries), "coming to
evening"
(yes, no are entries). Some have responded, many have not yet. All are
text
entries. How do I create formulas to sum the adults attending morning,
adults attending evening and the same for children and teens? I've tried
using the IF, COUNT and COUNTIF functions, but none seem to work for text
data in more than one column. Thanks

Glad that it worked for you.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"lippbj" wrote in message
...
Thanks very much. You solved my problem.

"Sandy Mann" wrote:

Column B = "Adult"
Column C = "Child"
Column D = "Tean"
Column E = "Morning"
Column F = "Evening"

Count of Adults coming to Morning:

=SUMPRODUCT((B2:B100="Yes")*(E2:E100="Yes"))

Count of Tean comin to both:

=SUMPRODUCT((D2:D100="Yes")*(E2:E100="Yes")*(F2:F1 00="Yes"))

and so on

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"lippbj" wrote in message
...
This should be simple but I can't figure it out. I have created a
table
to
tabulate the number of guests to a party. Some are adults, some are
children, some are teens. Some are invited to only the morning event,
some
to both morning and evening. Columns are "age category" (adult, child,
teen
are entries), "coming to morning" (yes, no are entries), "coming to
evening"
(yes, no are entries). Some have responded, many have not yet. All
are
text
entries. How do I create formulas to sum the adults attending morning,
adults attending evening and the same for children and teens? I've
tried
using the IF, COUNT and COUNTIF functions, but none seem to work for
text
data in more than one column. Thanks

Use an array formulae as shown below ( the formulae gives the list of
adults who will not come in the evening )


Guest name Age Category coming to morning coming to evening
a1 adult yes no
a2 teen yes no
a3 child yes no
a4 adult yes yes
a6 child no no


=SUM((B2:B6="adult")*(D2:D6="no"))

Please ctrl+shift+enter to enter the array formulae


HTH
Regards
Anirudh

On Jan 13, 10:13*pm, lippbj wrote:
This should be simple but I can't figure it out. *I have created a table to
tabulate the number of guests to a party. *Some are adults, some are
children, some are teens. *Some are invited to only the morning event, some
to both morning and evening. *Columns are "age category" (adult, child, teen
are entries), "coming to morning" (yes, no are entries), "coming to evening"
(yes, no are entries). *Some have responded, many have not yet. *All are text
entries. *How do I create formulas to sum the adults attending morning,
adults attending evening and the same for children and teens? *I've tried
using the IF, COUNT and COUNTIF functions, but none seem to work for text
data in more than one column. *Thanks

Thanks, but this was a little too advanced for me. I went with the simpler
solution posted above. I appreciate your help however.

"Ag" wrote:

Use an array formulae as shown below ( the formulae gives the list of
adults who will not come in the evening )


Guest name Age Category coming to morning coming to evening
a1 adult yes no
a2 teen yes no
a3 child yes no
a4 adult yes yes
a6 child no no


=SUM((B2:B6="adult")*(D2:D6="no"))

Please ctrl+shift+enter to enter the array formulae


HTH
Regards
Anirudh

On Jan 13, 10:13 pm, lippbj wrote:
This should be simple but I can't figure it out. I have created a table to
tabulate the number of guests to a party. Some are adults, some are
children, some are teens. Some are invited to only the morning event, some
to both morning and evening. Columns are "age category" (adult, child, teen
are entries), "coming to morning" (yes, no are entries), "coming to evening"
(yes, no are entries). Some have responded, many have not yet. All are text
entries. How do I create formulas to sum the adults attending morning,
adults attending evening and the same for children and teens? I've tried
using the IF, COUNT and COUNTIF functions, but none seem to work for text
data in more than one column. Thanks