# need to add an if statement to aggregate

#### silentdragonfly

##### New Member
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
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.

Replies
0
Views
150
Replies
1
Views
105
Replies
3
Views
77
Replies
1
Views
219
Replies
3
Views
133

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.

### Which adblocker are you using?

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

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