Question --> Of the 10 individuals with the highest income, how many are married males?

I want to achieve the same in a single function.

Below is a database.

enter image description here

First I fetched data with Large and the applied vlookup and the used counifs to get the count. enter image description here

enter image description here

CodePudding user response：

You are close.

Why are you using IFERROR? Are there errors in the data or something else?

In the formula below, I used a Table and Structured references. An advantage is that the range references will automatically adjust. You can switch to normal addressing if you prefer.

I would use:

=COUNTIFS(myTable[Income],">="&LARGE(myTable[Income],10),myTable[Sex],"M",myTable[Married],"Yes")

With regard to your formula, you would think you could just SUM the results. Unfortunately, for any instance where there are duplicates (2 married men with same income in the top 10), your formula would return a 2 and not a 1 and the SUM would therefore be incorrect.

You can avoid that, with your approach, by converting any non-zero into a 1:

=SUM(--ISNUMBER(1/(COUNTIFS(myTable[Income],LARGE(myTable[Income],SEQUENCE(10)),myTable[Sex],"M",myTable[Married],"Yes"))))

However, I think my approach is simpler.