You are here:

Excel/Aggregate function

Advertisement


Question
Hi I have a spreadsheet that has a table of data on it. Date Rec's, Date Withdrawn, Qtr End, Year Product, Account Name, Consultant, Status, Tax Status, Contribution, Withdrawal.

On another spreadsheet I have some formula's that calculate the Number of new accounts, closed account, inflows, outflows and also their respective total assets value, based off the value I put for quarter end in cell C2.

then underneath that I have a drop down box with the status and a cell that calculates the count.

I am trying to use the following formula to extract the data and give me a list of the members with the qtr end date, account name, product, contribution, withdrawal, and consultant, but it is not giving me what I need based off the specific date and status I choose.

=INDEX(Flows!$A$2:$I$1681,AGGREGATE(15,6,(ROW(Flows!$I$2:$I$1681)-ROW(Flows!$I$2)+1)/((Flows!$I$2:$I$1681='Firm - Qrtly'!$B$16)),COLUMNS($B20:F20)),MATCH($B$19,Flows!$A$1:$L$1,0))


where a2:I1681 is the range of data on from the table
B16 is the account status
B20 to F20 is where I want the list display to start
and B19 is the first piece of criteria in the list

Do you know where I am going wrong?

Answer
Usually I can picture what someone is trying to do, not in this case.

Can you send me a sample file showing some data as it is normally, and a manual mockup of the results you're trying to accomplish?

My contact site/address is below in my profile.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.