Based on visible rows calculate (COUNTIF, MINIF, AVERAGEIF, MAXIF)

[WaqasTariq]

I am looking to calculate the "Cst Info" data based on the filtered data available on the "Data" worksheet. I have so far tried searching the forum, but have been unable to find something similar.

Example workbook uploaded here.

This is how my "Data" worksheet looks like (after filtering for "Invoice Date"):

Inv	Bill To	N/A	N/A	Invoice Date	N/A	N/A	N/A	N/A	Days Open	Days Overdue
2568​	Company A			06/28/22​					16​	59​
6286​	Company A			06/30/22​					104​	2​
10004​	Company A			12/29/22​					32​	0​
13722​	Company A			06/14/23​					22​	170​
17440​	Company B			09/14/23​					200​	144​
39748​	Company A			11/30/22​					119​	89​

On another worksheet, "Cst Info" I am calculating the totals based on Column B, and need the calculations to be based on visible rows.

Bill To	Inv Count	Min (overdue)	Avg (overdue)	Max (overdue)	Min (open)	Avg (open)	Max (open)
Company A	10​	0​	65.3​	170​	16​	78​	119​
Company B	7​	62​	94.29​	144​	92​	128.29​	200​
Company C	12​	62​	76.42​	129​	92​	112.75​	174​

Formulas:

COUNTIF(Data!B:B,A2)

AVERAGEIF(Data!B:B,A2,Data!K:K)

MAXIFS(Data!K:K,Data!B:B,A2)

MINIFS(Data!J:J,Data!B:B,A2)

AVERAGEIF(Data!B:B,A2,Data!J:J)

MAXIFS(Data!J:J,Data!B:B,A2)

 

[JamesCanale]

Downloading spreadsheets doesn't work for me, but the AGGREGATE function looks like your friend. The first argument will give you average, min, max, count,... - the second argument will give you options to ignore hidden cells (5). Then the array in question.

 

[WaqasTariq]

Downloading spreadsheets doesn't work for me, but the AGGREGATE function looks like your friend. The first argument will give you average, min, max, count,... - the second argument will give you options to ignore hidden cells (5). Then the array in question.

Thank you for pointing me in the right direction. I have tried a few different ways, but not getting any values when using the AGGREGATE.

Formula: =AGGREGATE(2,5,Data!B:B,A2)

Where:
2 = Count
5 = Don't include hidden rows
Data!B:B = Rows to count
A2 = Match cell value of A2 in Data!B:B to count

What am I doing wrong here?

 

[Fluff]

How about like
For B2

=ROWS(FILTER(Data!$B$2:$B$100,(Data!$B$2:$B$100=A2)*(MAP(Data!$B$2:$B$100,LAMBDA(m,SUBTOTAL(103,m))))))

And for C2

=MIN(FILTER(Data!$K$2:$K$100,(Data!$B$2:$B$100=A2)*(MAP(Data!$B$2:$B$100,LAMBDA(m,SUBTOTAL(103,m))))))

 

[WaqasTariq]

How about like
For B2

=ROWS(FILTER(Data!$B$2:$B$100,(Data!$B$2:$B$100=A2)*(MAP(Data!$B$2:$B$100,LAMBDA(m,SUBTOTAL(103,m))))))

And for C2

=MIN(FILTER(Data!$K$2:$K$100,(Data!$B$2:$B$100=A2)*(MAP(Data!$B$2:$B$100,LAMBDA(m,SUBTOTAL(103,m))))))

Thank you! This is working as expected, but I have over 350 customers (unique column A customers), when I try to autofill the entire 350 rows with the logic, my computer halts with 100% cpu usage to Excel . Waiting for 30 mins and then killed Excel.

Is there a formula that could do this and not take so much cpu processing time?

 

[Fluff]

You could use a helper cell with

=SUBTOTAL(3,B2)

and then add that as another criteria to your existing formulae like

=MINIFS(Data!K:K,Data!B:B,A2,Data!L:L,1)

 

[WaqasTariq]

You could use a helper cell with

=SUBTOTAL(3,B2)

and then add that as another criteria to your existing formulae like

=MINIFS(Data!K:K,Data!B:B,A2,Data!L:L,1)

Thank you so much for the help Fluff. I appreciate it a lot.

Thoughts and prayers!

 

[Fluff]

You're welcome & thanks for the feedback.