I have the following data collected:

persnum: Person number within the household Age:age of the person, htval: height of the person

I have highlighted them in the screenshot.

I am looking for a formula that tells how many people does the tallest female in the survey live with.

I know that I need to use the max function, the IF function but I am missing the function that returns the persnum as I don't know which to use for this.

This is my formula so far (missing the persnum part)

=MAX(IF(data!G11:G9291=2,data!T11:T9291))

Thank you for your help

CodePudding user response：

You can try something like this:

=ROWS(FILTER(B11:B9291, T11:T9291=MAXIFS(T11:T9291,G11:G9291,2)))

The column you use on first input argument of FILTER is irrelevant, it just needs to have the same shape as the second input argument. It assumes the hight is represented in column T. You can add as many conditions as you want to MAXIFS in a similar way, to include additional constraints (the only restriction is that it needs to be a range, not an array). For example I don't know how you identify the person is live based on the input data.

You can also use the entire column, if it makes sense for your case. As the following example: