Will subtotal work with arrays?

[Joel John]

I got column A with contract values (in dollars) and column B with the amount invoiced in a month. The column B will have both the numeric values (in dollars) and percentages with respect to the contract value (same row in column A).
I have a formula to get the total sum of amount invoiced in a month in column B as below:
=SUM(IF(B:B<=1,B:B*A:A,0),IF(B:B>1,B:B))
But the formula that I got doesn't work when I use filters on the list. Will subtotal work on arrays in anyway?

 

[Jeffrey Mahoney]

Add a helper column and add this formula in C2 down:
=SUBTOTAL(103,A2)

Then you can use that to multiple the values in column C. Visible = 1, hidden = 0

Book2
	A	B	C	D	E	F
1	Contract Values	Amount Invoiced	Visible		Sum:	$ 178,309.00
2	$ 10,000.00	$ -	1
3	$ 10,000.00	$ -	1
4	$ 10,000.00	20%	1
5	$ 10,000.00	$ 50,000.00	1
6	$ 10,000.00	30%	1
7	$ 10,000.00	$ 2,501.00	1
8	$ 10,000.00	25%	1
9	$ 10,000.00	$ 50,001.00	1
10	$ 10,000.00	80%	1
11	$ 10,000.00	$ 2,502.00	1
12	$ 10,000.00	22%	1
13	$ 10,000.00	$ 50,002.00	1
14	$ 10,000.00	31%	1
15	$ 10,000.00	$ 2,503.00	1
Sheet1

Cell Formulas
Range	Formula
F1	F1	=SUM(IF(B2:B15<=1,B2:B15*A2:A15*C2:C15,0),IF(B2:B15>1,B2:B15*C2:C15))
C2:C15	C2	=SUBTOTAL(103,A2)

 

[Joel John]

Hi Jeffrey,

Thanks for the answer but this is not what I was looking for. We have multiple columns with 100s of lines of data which spreads across few years of data.
So, I did something similar to what you mentioned before by including a duplicate sheet to carry the years of data but this just increases the file size too much which is already a huge file and creates a lot of syncing issues within our server.
I was wondering if we can use subtotal function in some way to use the array that I get from the if function.

 

[Fluff]

How about

=SUM(BYROW(A2:B15,LAMBDA(br,IF(SUBTOTAL(103,br),IF(INDEX(br,,2)<=1,INDEX(br,,2)*INDEX(br,,1),INDEX(br,,2))))))

 

[Joel John]

How about

=SUM(BYROW(A2:B15,LAMBDA(br,IF(SUBTOTAL(103,br),IF(INDEX(br,,2)<=1,INDEX(br,,2)*INDEX(br,,1),INDEX(br,,2))))))

Thank you for the answer! That worked in my example. But in my actual sheet, I have about 14 columns in between my contract value column and amount invoice column, and then there are multiple columns with the amounts invoiced in each month. Is it possible with this formula to get the total invoiced for each month separately on top of each column?

 

[Fluff]

For the 1st part you can something like

Fluff.xlsm
	A														O	P	Q
1	Contract Values														Amount Invoiced		20306
2	10000														0
3	10000														0
4	10000														0.2
6	10000														0.3
7	10000														2501
8	10000														0.25
11	10000														2502
12	10000														0.22
14	10000														0.31
15	10000														2503
Sheet4

Cell Formulas
Range	Formula
Q1	Q1	=SUM(BYROW(A2:O15,LAMBDA(br,IF(SUBTOTAL(103,br),IF(INDEX(br,,15)<=1,INDEX(br,,15)*INDEX(br,,1),INDEX(br,,15))))))

 

[Joel John]

I understand, now I can just add another row to indicate the column number for each month and use that instead of 15 and so on. cheers
Thank you for the help. Really appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback.