Membership list: column for no. of over 18s?
 

I'm updating a club membership list in Excel at the moment.

Each member (or family membership) has a single row and the dates of
birth (and current ages calculated therefrom) are displayed in
individual cells.

What I'm trying to do now is create another column in the spreadheet,
headed "Number of over 18s" (ie, the number of people in that family who
are aged over 18). So, if columns D, F, and H contain the ages of 3
people, what formula do I need to put in my new column, K?

(ie, if cell D4 is 53, cell F4 is 50, and cell H4 is 15, then the output
displayed in cell K4 would be "2")

Thanks for any help
David

Membership list: column for no. of over 18s?
 

One way...

=INDEX(FREQUENCY((D4,F4,H4),18),2)

--
Biff
Microsoft Excel MVP


"Lobster" wrote in message
...
I'm updating a club membership list in Excel at the moment.

Each member (or family membership) has a single row and the dates of birth
(and current ages calculated therefrom) are displayed in individual cells.

What I'm trying to do now is create another column in the spreadheet,
headed "Number of over 18s" (ie, the number of people in that family who
are aged over 18). So, if columns D, F, and H contain the ages of 3
people, what formula do I need to put in my new column, K?

(ie, if cell D4 is 53, cell F4 is 50, and cell H4 is 15, then the output
displayed in cell K4 would be "2")

Thanks for any help
David

Membership list: column for no. of over 18s?
 

I use an array formula:

={SUM(D218,F218,H218)}

When you enter an array formula (array formula: A formula that
performs multiple calculations on one or more sets of values, and then
returns either a single result or multiple results. Array formulas are
enclosed between braces { } and are entered by pressing CTRL+SHIFT
+ENTER.), Microsoft Excel automatically inserts the formula between
{ } (braces).

Membership list: column for no. of over 18s?
 

T. Valko wrote:
One way...

=INDEX(FREQUENCY((D4,F4,H4),18),2)


Wow - that was quick, and such an simple formula! I've been scratching
my head with massive long IF statements and getting nowhere (not
surprisingly).

Thank you so much!
David

(actually it needs to contain "17" not "18" to work correctly!)

Membership list: column for no. of over 18s?
 

the number of people in that family who are aged over 18 (actually it
needs to contain "17" not "18" to work correctly!)

Then your criteria isn't "who are aged over 18". Maybe you meant "who are
aged 18 and over".

17...17...18

=INDEX(FREQUENCY((D4,F4,H4),18),2)

Returns 0 - no age *over* 18

=INDEX(FREQUENCY((D4,F4,H4),17),2)

Returns 1 - 1 age 18 and over

--
Biff
Microsoft Excel MVP


"Lobster" wrote in message
...
T. Valko wrote:
One way...

=INDEX(FREQUENCY((D4,F4,H4),18),2)


Wow - that was quick, and such an simple formula! I've been scratching my
head with massive long IF statements and getting nowhere (not
surprisingly).

Thank you so much!
David

(actually it needs to contain "17" not "18" to work correctly!)