rcomag
New Member
- Joined
- Aug 23, 2019
- Messages
- 37
- Office Version
- 2013
- 2010
- Platform
- Windows
=IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=D6)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C6),1))),1)
Thank you very much sir! If I use it to look for multiple sheets, =IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=D6)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C6),1))),1....., Could I copy the formula and paste it at the end of the formula, will just change the sheet name.. Should I start copying from the iferror or just the aggregate.... '=IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C27)*(barobaybay!$J$13:$J$2000>=D27)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C27)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C27),1))),1))*(AGGREGATE(15,6,(bell!$I$13:$I$2000)/((bell!$A$13:$A$2000=C27)*(bell!$J$13:$J$2000>=D27)),1),AGGREGATE(15,6,(bell!$I$13:$I$2000)/((bell!$A$13:$A$2000=C27)*(bell!$J$13:$J$2000>=AGGREGATE(14,6,(bell!$J$13:$J$2000)/(bell!$A$13:$A$2000=C27),1)))Try this:
Excel Formula:=IFERROR(AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=D6)),1),AGGREGATE(15,6,(barobaybay!$I$13:$I$2000)/((barobaybay!$A$13:$A$2000=C6)*(barobaybay!$J$13:$J$2000>=AGGREGATE(14,6,(barobaybay!$J$13:$J$2000)/(barobaybay!$A$13:$A$2000=C6),1))),1)
Sir, what if there are duplications in the number but with different names and value.You're welcome.
Why you don't replace previous formula with this. whenever you need you can sheet name and range based your data range. No problem.