Countif- Visible Cells in Filter mode with additional values return

[Rita Rees]

Hi – there was a formula solution posted (shown below), that allows a count of visible cells only, for a specific ‘criteria’ needed only – i.e. using the dummy data below, it would allow me a count of only ‘Closed won’ fields, that would also work when filtering other columns, like date or client etc

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-MIN(ROW(B2:B50)),,1))*(B2:B50="Closed won"))

My question is how can I expand on this formula to also get a return on what the £ values shown in another column would be when using this formula?
i.e. using the dummy info below, I'm using the formula to count, 'wins' and 'losses' (Data 1 column) and then having a simple formula to calculate a win rate, but I also need to return what the £ values would be when using this formula (in this case using value 1 or value 2 columns), so that once it 'counts' the number of wins/losses, it also gives me the total £ values of those selected – that will also work when filtering other columns
Are you able to help with this?
Thanking you in advance

Data 1	Data 2	Data 3	Data 4	Data 5	Value 1	Value 2
Suspect	01/04/2023	01/06/2023	01/08/2023	01/09/2023	£80,000,000	£1,000,000
Negotiation	15/10/2021	03/12/2021	01/12/2021	03/05/2022	£30,000,000	£22,800,000
Negotiation	28/09/2020	15/03/2021	09/03/2021	01/07/2021	£17,371,798	£8,405,000
Closed won	01/06/2022	30/09/2022	01/01/2023	01/03/2023	£15,000,000	£15,000,000
Prospect	17/10/2022	14/11/2022	16/01/2023	13/02/2023	£15,000,000	£6,750,000
Prospect	17/10/2022	14/11/2022	16/01/2023	13/02/2023	£15,000,000	£5,250,000
Bidding	23/08/2021	08/04/2022	23/08/2022	28/02/2023	£13,758,412	£6,879,206
Bidding	23/08/2021	08/04/2022	23/08/2022	28/02/2023	£13,758,412	£6,780,932
Closed won	22/03/2022	05/04/2022	22/07/2021	01/06/2022	£10,000,000	£10,000,000
Closed won	05/11/2021	17/12/2021	25/11/2021	29/07/2022	£9,500,000	£9,500,000
Bidding	26/10/2021	13/12/2021	27/05/2022	01/11/2022	£9,000,000	£4,959,000
Closed won	24/02/2021	26/03/2021	02/02/2021	28/06/2021	£9,000,000	£9,000,000
Closed won	07/06/2021	14/06/2021	05/08/2021	11/10/2021	£9,000,000	£9,000,000
Closed won	10/03/2023	28/04/2023	30/06/2023	29/09/2023	£4,000,000	£4,000,000
Closed won	29/07/2022	31/08/2022	30/09/2022	30/11/2022	£3,000,000	£3,000,000
Suspect	01/06/2023	13/07/2023	02/03/2024	02/05/2024	£2,500,000	£2,500,000
Closed won	02/08/2021	06/09/2021	21/12/2021	02/05/2022	£2,500,000	£2,500,000
Closed lost	05/07/2021	09/08/2021	27/09/2021	30/04/2022	£2,200,000	£0

 

[Fluff]

Hi & welcome to MrExcel.
Rather than using volatile functions, why not use a helper column instead like

++Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Data 1	Data 2	Data 3	Data 4	Data 5	Value 1	Value 2
2		Suspect	01/04/2023	01/06/2023	01/08/2023	01/09/2023	£80,000,000	£1,000,000	1		5
3		Negotiation	15/10/2021	03/12/2021	01/12/2021	03/05/2022	£30,000,000	£22,800,000	1		34000000
8		Bidding	23/08/2021	08/04/2022	23/08/2022	28/02/2023	£13,758,412	£6,879,206	1
9		Bidding	23/08/2021	08/04/2022	23/08/2022	28/02/2023	£13,758,412	£6,780,932	1
11		Closed won	05/11/2021	17/12/2021	25/11/2021	29/07/2022	£9,500,000	£9,500,000	1
12		Bidding	26/10/2021	13/12/2021	27/05/2022	01/11/2022	£9,000,000	£4,959,000	1
13		Closed won	24/02/2021	26/03/2021	02/02/2021	28/06/2021	£9,000,000	£9,000,000	1
14		Closed won	07/06/2021	14/06/2021	05/08/2021	11/10/2021	£9,000,000	£9,000,000	1
15		Closed won	10/03/2023	28/04/2023	30/06/2023	29/09/2023	£4,000,000	£4,000,000	1
17		Suspect	01/06/2023	13/07/2023	02/03/2024	02/05/2024	£2,500,000	£2,500,000	1
18		Closed won	02/08/2021	06/09/2021	21/12/2021	02/05/2022	£2,500,000	£2,500,000	1
19		Closed lost	05/07/2021	09/08/2021	27/09/2021	30/04/2022	£2,200,000	£0	1
20
Main

Cell Formulas
Range	Formula
K2	K2	=COUNTIFS(B:B,"Closed won",I:I,1)
K3	K3	=SUMIFS(G:G,B:B,"Closed won",I:I,1)
I2:I3,I8:I9,I11:I15,I17:I19	I2	=SUBTOTAL(3,B2)

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Main!$B$1:$I$18	K2:K3