Forum Discussion
Solved
Help with multiple conditional statements in Excel
Below is an example of a much larger dataset that I am working with. What I am having trouble figuring out is how to write a formula that does the following: Find a certain “Vendor Name” in column...
I don't seem to have the MAXIFS function in my version of Excel. Is there any way to use the AGGREGATE function in place of the MAXIFS function?
Try the following array formula, confirmed with Ctrl+Shift+Enter:
=INDEX($E$2:$E$200, MATCH(1, ($A$2:$A$200=G2)*($D$2:$D$200=MAX(IF($A$2:$A$200=G2, $D$2:$D$200))), 0))
- This works great. Would you know a way to update this to also filter out any "Rule" that equals zero?
In your screenshot, the rules are text values. How would they be zero?
- Sorry I should have been more thorough in the example. There are some "Rules" that are blank in my actual data set that appear as zero when I run your formula. Does that make sense?
Also, do you know a way that this formula could be modified to calculate the 2nd, 3rd, 4th and 5th "Rule" with the highest "Total"?