Calculate adjacent column with Subtotal

[deacon10]

Hi there, I am hoping someone can help,

I have the following data with a filter applied in A4 & B4

A3 is currently calculated with =SUBTOTAL(9,A5:A13).

Does anyone know how I can calculate the subtotal in B3 of column A5:A13, if the corresponding cell in B5:B13 is not blank?

In this example B3 would = 548

Any help eternally appreciated,

CF

 

[Eric W]

Try:

B3:

=SUMPRODUCT(A5:A13,--(B5:B13="x"),SUBTOTAL(102,OFFSET(A5,ROW(A5:A13)-ROW(A5),0)))

 

[Dave Patton]

T202306a.xlsm
	A	B	C
1
2	548		With a Table
3	548	548	Without table
4	Amount	Marker
5	277	x
6	167	x
7	78	x
8	17	x
9	9	x
13	0	x
1c

Cell Formulas
Range	Formula
A2	A2	=SUBTOTAL(109,A5:A13)
A3	A3	=SUMPRODUCT(--(B5:B13>" "),A5:A13)
B3	B3	=SUMPRODUCT(--(B5:B13="x"),A5:A13)

 

[deacon10]

Try:

B3:

=SUMPRODUCT(A5:A13,--(B5:B13="x"),SUBTOTAL(102,OFFSET(A5,ROW(A5:A13)-ROW(A5),0)))

Massive thanks to @Eric W & @Dave Patton for these solutions. They work great

​