Nesting FILTER function inside of the AGGREGATE function?

[diversification]

Hi there, I have a data set that looks like what I've written, below. I want Row 1 of this Sheet to have a totaling column that will:

1. Sum all values below Row 1 within their column
2. Exclude any Rows that contain the word "total" in Column A
3. Exclude any Rows that I hide manually or hide by filtering

I was successful writing a formula to complete 1 & 2, but 3 is posing an issue.

Here's the formula to meet requirements 1 and 2 (it references only Column C, but I have it dragged across Row 1):

=SUM(FILTER(C$2:C$100, (ISNUMBER(C$2:C$100))*(ISERROR(SEARCH("total",$A2:$A100)))))

To complete requirement 3, I had planned on replacing SUM with AGGREGRATE in the following manner:

=AGGREGATE(9,3,FILTER(C$2:C$100, (ISNUMBER(C$2:C$100))*(ISERROR(SEARCH("total",$A2:$A100)))))

Unfortunately, this doesn't work and reports some sort of error when I try to use it. Does anyone have any alternative suggestions?

Date	Type	Quantity	Cost
11/1/2022	Orange	100	5000
12/24/2022	Blue	50	10000
1/1/2023	Green	150	1000
SubTotal:		300	16000
Date	Type	Quantity	Cost
12/17/2022	Purple	5000	50
Subtotal:		5000	50
Total:		5300	16050

My goal is to nest AGGREGATE and FILTER functions to allow me to apply filters to a column of data, and then

 

[Domenic]

Unfortunately, the aggregate function does not accept an array for functions such as SUM, AVERAGE, COUNT, etc. It will only accept one for functions such as LARGE, SMALL, etc. Therefore, try the following formula instead...

=SUM(FILTER(C$2:C$11,SUBTOTAL(109,OFFSET($A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2),0,1))))

Note that the formula returns #CALC! when no row meets the criteria. To return 0 instead, try the following formula...

=SUM(FILTER(C$2:C$11,SUBTOTAL(109,OFFSET($A$2:$A$11,ROW($A$2:$A$11)-ROW($A$2),0,1)),0))

Hope this helps!