counting entries in 2 columns

  • Thread starter Thread starter lippbj
  • Start date Start date
L

lippbj

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
 
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:F100="Yes"))

and so on

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Thanks very much. You solved my problem.

Sandy Mann said:
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:F100="Yes"))

and so on

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
Click to expand...
 
Thanks, but this was a little too advanced for me. I went with the simpler
solution posted above. I appreciate your help however.
 
Glad that it worked for you.

--
Regards,

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top