Sum of invoices in a filter, counting only visible invoices per aging group.

[Petervz]

Data :
Payor Payor Name Client Client Name Matter Matter Name Bill Date Bill Number Outstanding A/R Aging
51234 Payor1 41234 Client 11 12345678 Mr Excel 16-09-14 87654321 € 1.000,00 400
51235 Payor2 41235 Client 12 12345679 Mr Excel 20-10-14 87654322 € 2.000,00 366
51236 Payor3 41236 Client 13 12345680 Mr Excel 04-04-15 87654323 € 3.000,00 200
51237 Payor4 41237 Client 14 12345681 Mr Excel 05-04-15 87654324 € 4.000,00 199
51238 Payor5 41238 Client 15 12345682 Mr Excel 18-05-15 87654325 € 5.000,00 156
51239 Payor6 41239 Client 16 12345683 Mr Excel 05-06-15 87654326 € 6.000,00 138
51240 Payor7 41240 Client 17 12345684 Mr Excel 15-07-15 87654327 € 7.000,00 98
51241 Payor8 41241 Client 18 12345685 Mr Excel 20-08-15 87654328 € 8.000,00 62
51242 Payor9 41242 Client 19 12345686 Mr Excel 21-08-15 87654329 € 9.000,00 61
51243 Payor10 41243 Client 20 12345687 Mr Excel 05-09-15 87654330 € 10.000,00 46
51244 Payor11 41244 Client 21 12345688 Mr Excel 07-10-15 87654331 € 11.000,00 14
51245 Payor12 41245 Client 22 12345689 Mr Excel 15-10-15 87654332 € 12.000,00 6

Count of visible invoices : Formula =SUBTOTAL(3;H10:H10000)
Sum of visible invoices : Formula =SUBTOTAL(9;I10:I10000)
Avg of ageing visible invoices : Formula =SUBTOTAL(1;J10:J10000)

As i now want to calculate how many (visible) invoices are 361+ , 180-360 and so on.
I tried MrExcel's Learn Excel #946 - Countif Filtered but i couldn't get it right. Nor i succeeded with Sumproduct

Using Microsoft Excel 2010

I am a Credit Control Specialist and would like to make a good reporting sheet. Thank you for your input in this !!

 

[Petervz]

I meant the cell range...

J10:J10000 is where i have calculated the age with Formula =TODAY()-G10
So if in column J a (visible) invoice age is 360 or over it should be counted.
I would like to copy the formula for all other ranges too for reporting.

Thank you for your help. I am very very thankful and i really appreciat all help in this.

 

[Aladin Akyurek]

J10:J10000 is where i have calculated the age with Formula =TODAY()-G10
So if in column J a (visible) invoice age is 360 or over it should be counted.
I would like to copy the formula for all other ranges too for reporting.

Thank you for your help. I am very very thankful and i really appreciat all help in this.

In what follows, replace comma's with semi-colons on your system

Given your subtotal specs:

[quote[Count of visible invoices : Formula =SUBTOTAL(3;H10:H10000)
Sum of visible invoices : Formula =SUBTOTAL(9;I10:I10000)
Avg of ageing visible invoices : Formula =SUBTOTAL(1;J10:J10000)[/quote]

1. Just enter:

=SUMPRODUCT(SUBTOTAL(2,OFFSET(H10,ROW(H10:H10000)-ROW(H10),0,1)),--(J10:J10000>=180),--(J10:J10000<=360))

2. Just enter:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(I10,ROW(I10:I10000)-ROW(I10),0,1)),--(J10:J10000>=180),--(J10:J10000<=360))

3. Control+shift+enter, not just enter:

=AVERAGE(IF(SUBTOTAL(2,OFFSET(I10,ROW(I10:I10000)-ROW(I10),0,1)),IF(J10:J10000>=180,IF((J10:J10000<=360,I10:I10000))))

 

[Petervz]

Great Job Aladin,

I had to change some minor things but it works !!!!
I am very happy with the results.

 

[Aladin Akyurek]

Great Job Aladin,

I had to change some minor things but it works !!!!
I am very happy with the results.

Good to hear that. Thanks for the update.