How do I add a criteria to the aggregate function in this excel formula?

May 9, 2020 7:45 AM

I've been working with this Excel formula for a month or so. It comes from Leila Gharani's Youtube tutorial.

=IF(ROWS($A$1000:$A1000)<$J$291,INDEX($B$2:$B$300,AGGREGATE(15,3,($N$2:$N$300="Japanese")/($N$2:$N$300="Japanese")*ROW($N$2:$N$300)-ROW($N$1),ROWS($A$1000:$A1000)))," ")

In this iteration, it's indexing column B, which is a list of movie names, and returning a list of every Japanese language film. Film languages are listed in column N. The formula takes advantage of Aggregate's "Ignore error" option; since Excel treats yeses as 1's and nos as 0's, dividing the aggregate results by itself returns an error for all the nos, since you can't divide by zero. Pretty clever. Then the formula multiplies the 1 by the row where it's located, and finally returns the smallest number in the list to the index function (then the second smallest, then third smallest as you drag down the formula).

My question is, how do I add criteria so the film not only has to be in Japanese, but also has to have a RottenTomatoes score of >75%, if Column T is RottenTomatoes scores? I'm feel I should just multiply the Japanese criteria by the RT criteria in brackets and then divide that product by itself, but I keep getting errors when I try this. Maybe my syntax is screwy?

And yes, I know it would be a lot easier to do this using VBA, but I'm running the workbook on Sharepoint, which doesn't support VBA.

Thanks!

posted by jwhite1979 to Computers & Internet (2 answers total)

Response by poster: I apologize for wasting anyone's time. I just figured out what I was doing wrong. I was only getting an error when the returned value was false, i.e., when there were no instances of films matching both criteria in the position the formula was looking at. Where there was a match, the formula worked. At the beginning of my formula, there is an if statement to take care of that situation, returning a " " instead of an error. To do this, the formula referenced another cell that checked how many instances of the matching criteria exist. Anyway, I had failed to update THIS reference cell with the updated criteria. Once I did, everything worked okay.

This is the final formula:
=IF(ROWS($A$1000:$A1000)<>
And this is the helper cell.
=(COUNTIFS(N2:N300,"Japanese",E2:E300,"="&8.1))

Mods, no hard feelings if you delete this whole thread. Or maybe it could be helpful for posterity. Either way, thanks.
posted by jwhite1979 at 8:03 AM on May 9, 2020 [1 favorite]

Glad we could help. Not a waste of time. I find it almost one of those cosmic unwritten rules. As soon as you ask for help, you find the answer on your own. If you did not post, you never would have found it.
posted by AugustWest at 9:58 AM on May 9, 2020 [1 favorite]