need to add an if statement to aggregate

silentdragonfly

New Member
Joined
Dec 19, 2014
Messages
25
I have a database tab with a list of transactions done on loans that includes payments and advances. I created a tab for a user to input an account number and have it review all the transactions in the loan activity tab and return every transaction related to that one loan. My goal is to have it list each transaction but I want to put the payments in one column and the advances in another (columns M & N). Right now both payments and advances are populating in column N. Here is the formula in column N:

=IF(ROWS(N$7:N7)>COUNTIF('Loan Activity'!$AI$2:$AI$2836,$B$1),"",INDEX('Loan Activity'!$P$2:$P$2836,AGGREGATE(15,6,(ROW('Loan Activity'!$AI$2:$AI$2836)-ROW('Loan Activity'!$AI$2)+1)/('Loan Activity'!$AI$2:$AI$2836=$B$1),ROWS(N$7:N7))))

I think what I need is an if statement before "AGGREGATE" (IF Loan Activity transaction column = P put in Payment column, if Loan Activity transaction column = A put in Advance column) but I can't make it work.

I also read a post that seemed to say that I could multiply the Array part of the AGGREGATE argument by Loan Activity transaction column = A or P but again, I can't make it work.

Any help would be appreciated.
Peg
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,668
Office Version
  1. 365
Platform
  1. Windows
How about posting up some sample data using XL2BB so that we can actually play with the data to provide a solution. Really only need a handful (5-8) sample records.
 

Forum statistics

Threads
1,148,342
Messages
5,746,192
Members
423,997
Latest member
eakenila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top