1

Background

I'm making a spreadsheet that will plan out attendees for a wedding. There will be separate halls for men and women, and we are debating which age groups should be invited. Right now I have a tab in my sheet called age range and it has the following age ranges:

  • 0 - 3
  • 4 - 12
  • 12 - 18
  • 18+

I have a tab that has different families, along with their gender, age range etc listed like so:

WA109207 question first example

Here is the summary sheet:

WA109207 question second example

Question

The goal of doing this spreadsheet is to be able to quickly make decisions like: do we want to invite everyone from family x/y/z with everyone above 12 years old, etc.?

What I would like to do in the spreadsheet is, if I select the dropdown of age group and select, say, 4-12, then I want everyone in the age group 4-12 (and the ones older) to be selected. Right now if I select 4-12, then only the people who are 4-12 years old get listed.

Ideas?

(note: keep in mind I want the age group that is as equal or older, not the other way around. If the age group 18+ was selected, I don't want anyone younger than 18 to be included.)

WA109207 question third example

Here is an editable copy of the spreadsheet

What I tried

I was able to do this for a single value: tell me if the person belonging to a specific cell in the heba_immediate_family table belongs to an age range group that is equal to or greater than the one you selected, by running this:

=match(vlookup(C23, 'age range'!$A1:$B4, 2),

{vlookup(heba_immediate_family!D2,'age range'!$A1:$B4,2)},1)

However I don't know how to expand this to include the whole table, i.e., this formula:

=ARRAYFORMULA(match(vlookup(C24, 'age range'!$A1:$B4, 2),vlookup(heba_immediate_family!D2:D19,'age range'!$A1:$B4,2),1))

returns this error

Did not find value '0' in MATCH evaluation.

Ideas?

  • 1
    Asking "Ideas?" makes the question opinion based, and asking "Ideas?" make it worst (: Anyway, here is an idea: On the first sheet, add a column to hold the age of each family member, then use the filter option filter by condition > "Greater than or equal to" – Rubén Aug 30 '17 at 21:18
  • You can achieve some indirection and improve function clarity by using named ranges in your spreadsheets. Added benefit it that if the range is moved or expanded, there is no need to update the formulas that use it since the name references it, and all you need to do is keep the definition of the range up to date. – Gypsy Spellweaver Aug 31 '17 at 7:15
1

The use of MATCH was, indeed, was the way to go, adding that inside the COUNTIFS that you started with. (Corrected for the first row of the ranges.)

COUNTIFS(heba_immediate_family!C2:C, B11, heba_immediate_family!D2:D, C11)

As it is in the screen shot, the first criteria in your COUNTIFS is okay, so we'll start by rebuilding the second criteria. More specifically, what the range is matched against, which is shown as C11 in the sample. Because you need to create a cumulative selection rather than a single selection we need to compare the cell range against the age range using an inequality operator. With nothing given, the COUNTIFS implicitly defaults to = for comparisons. We need to change that to an explicit comparison using >=, greater than or equal, so that it matches anything in the selected range, or greater. This yields:

COUNTIFS(heba_immediate_family!C2:C, B11, heba_immediate_family!D2:D, ">="&C11)

The next problem is that your case is using what is really a text string in what we think of as a numerical range. To fix that is where we need to use the MATCH function. MATCH returns the position of the matched item in the range relative to the start of the range. You have already prepared for this with the age range sheet, which we can use. We don't however need the second column since we're going to use the MATCH function rather than the VLOOKUP function. The numbers we will get from MATCH will, however, be the same as what's listed in the second column, so it does serve as a handy reference. To make this work, we need to use MATCH on both sides of the comparison so that we're getting the same reference point. The MATCH function normally works on one cell, so we also need the ARRAYFORMULA function to apply the first MATCH in an array sense. One last point is that the MATCH function defaults to assuming that the given range is sorted in ascending order. Since the age ranges are really text, they are not. We need to inform the function of this with the optional third argument set to 0, This now gives us:

COUNTIFS(heba_immediate_family!C2:C, B11, ARRAYFORMULA(MATCH(heba_immediate_family!D2:D, 'age range'!A1:A, 0)), ">="&MATCH(C11, 'age range'!A1:A, 0))

If you want to type this in to each cell on the summary sheet, we're done. If you want to be able to replicate it by dragging, then we need to lock down some of the range references with absolute notation, $. This will give us:

COUNTIFS(heba_immediate_family!C$2:C, B11, ARRAYFORMULA(MATCH(heba_immediate_family!D$2:D, 'age range'!A$1:A, 0)), ">="&MATCH(C11, 'age range'!A$1:A, 0))

One more thing we can do to make this all easier to use is to link the sheet names in the formula to the name in the first column. This step will require a change to how the sheets are named, or the way the families are named in the first column of the summary page, so that they are the same. In the first column of the summary sheet you have heba immediate family and it references the sheet named heba_immediate_family. Since typing the underscore is less natural than typing a space, I'd recommend renaming the sheets to remove the underscores, but any way that makes the two match will work. Having done that, the next part is to use the INDIRECT function to turn the text into a sheet reference. Actually, because of how it works, we will really need to turn that into a range reference using the text in the cell as part of it. The way we're going to do that is to concatenate the text in the cell with the fixed text of the range, like this for the age range column, INDIRECT(A3&"!D$2:D"). Doing that for both ranges, and moving it into the data first row of the summary sheet, gives the final version in cell D3, which can be reproduced down the sheet as needed for all the families. The completed formula is:

=COUNTIFS(INDIRECT(A3&"!C$2:C"), B3, ARRAYFORMULA(MATCH(INDIRECT(A3&"!D$2:D"), 'age range'!A$1:A, 0)), ">="&MATCH(C3, 'age range'!A$1:A, 0))

As a further option you could drop the gender column completely from the sheet. Everything in the top half is, by definition, "M", and in the bottom half is "F". So deleting column B moves the age ranges into column B and the formulas into column C. Doing that, and hard coding the gender into the formula gives two formulas.

For the Men, starting in cell C3:

COUNTIFS(INDIRECT(A3&"!C$2:C"), "M", ARRAYFORMULA(MATCH(INDIRECT(A3&"!D$2:D"), 'age range'!A$1:A, 0)), ">="&MATCH(C3, 'age range'!A$1:A, 0))

For the Women, starting in cell C11:

COUNTIFS(INDIRECT(A11&"!C$2:C"), "F", ARRAYFORMULA(MATCH(INDIRECT(A11&"!D$2:D"), 'age range'!A$1:A, 0)), ">="&MATCH(C11, 'age range'!A$1:A, 0))

And, since I see one final option that might be an improvement for use, I'll propose a final improvement, with an understanding. If the intention is to invite the same age range from all the families, rather than different ranges from each family, there is no need to have the age group selectable on each row of the summary sheet. Instead, make it a single cell at the top of the sheet and link all formulas to it. If, on the other hand, you are considering the option of different age ranges from different families, then this change is not what you would want to do.

To do this, combined with the last option about genders, again drop the column B of age group, moving the formulas, and totals, into column B. In cell D1 type "Age group:", and in cell E1 create the age range drop down. Then change the two formulas to these.

For the Men, starting in cell B3:

COUNTIFS(INDIRECT(A3&"!C$2:C), "M", ARRAYFORMULA(MATCH(INDIRECT(A3&"!D$2:D"), 'age range'!A$1:A, 0)), ">="&MATCH($E$1, 'age range'!A$1:A, 0))

For the Women, starting in cell B11:

COUNTIFS(INDIRECT(A11&"!C$2:C), "F", ARRAYFORMULA(MATCH(INDIRECT(A11&"!D$2:D"), 'age range'!A$1:A, 0)), ">="&MATCH($E$1, 'age range'!A$1:A, 0))
  • @pnuts Work it up and write an answer. It's bound to be better than mine, since I'm not even close to an expert in Google Sheets. – Gypsy Spellweaver Aug 30 '17 at 16:41
  • 2
    @pnuts I leave my answers even if the OP don't like them. Unless they are actually wrong that is. I figure that, while the OP started the ball with a question, the site serves others later, who might find my version useful instead. – Gypsy Spellweaver Aug 30 '17 at 16:56
  • 1
    typing COUNTIFS(heba_immediate_family!C2:C, B11, MATCH(heba_immediate_family!D2:D, 'age range'!A1:A, 0), ">="&MATCH(C11, 'age range'!A1:A, 0)) returns error: Array arguments to COUNTIFS are of different size. I guess that's explained by the documentation of countifs: Any additional ranges must contain the same number of rows and columns as criteria_range1. – abbood Aug 31 '17 at 6:47
  • @abbood My mistake. I mentioned the need for the ARRAYFORMULA function in the text, then forgot to use it in the code. Correcting it now. Watch for the edit. – Gypsy Spellweaver Aug 31 '17 at 6:52
  • 1
    I'll have to go over your recommendations for improvements a step at a time, b/c in my experience, making elaborate formulas reach a point where it becomes difficult to read, and i want the formulas in my sheet to be as readable and maintainable as possible. That balance is much easier to achieve while writing actual code (b/c you can simply wrap up complexity in separate functions and name them in english), such indirection is not as easy to do with spreadsheets – abbood Aug 31 '17 at 7:08
0

Add a column with flags, say 0, for baby, 1 for child, 2 for teenager, 3 for adult then use Filter by condition... and greater than (for adults 2) on that column with range selected to suit.

  • mmm.. still confused.. examples? – abbood Aug 29 '17 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.