Sum values(column C) based on unique value(column A) after filtering(column B)

[liakos]

Hello everybody,

I need some help creating a formula.
I have an excel file with 3 columns and I need to sum values of column C, based on the unique values of column A.
I achieved that using the formula '=SUMPRODUCT((C3:C13/COUNTIF(A3:A13;A3:A13)))' but when I use filter in column B, the result is not updated.

I attached a picture of a sample spreadsheet.
Could you please help me?

Thanks in advance,
Liakos

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[liakos]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hello Fluff, you are right...I did just now. Thanks!

 

[Fluff]

Thanks for that.
How about

=SUMPRODUCT((D3:D13=1)*(IFERROR(C3:C13/COUNTIFS(D3:D13,1,A3:A13,A3:A13),0)))

 

[liakos]

Thanks for that.
How about

=SUMPRODUCT((D3:D13=1)*(IFERROR(C3:C13/COUNTIFS(D3:D13,1,A3:A13,A3:A13),0)))

Thanks for your reply.
Using this formula I get zero result. Why do you evolve column D? I am asking because, I cannot understand how this formula works

 

[Fluff]

Sorry, forgot to mention you need a helper column in col D with

=SUBTOTAL(103,A3)

 

[liakos]

Sorry, forgot to mention you need a helper column in col D with

=SUBTOTAL(103,A3)

I added the helper column, but it's still the same...zero result

 

[Fluff]

Can you post some sample data using the XL2BB add-in as it works for me

Fluff.xlsm
	A	B	C	D
1				90
2
3	188612		20	1
4	188612		20	1
5	188614		30	1
6	188614		30	1
7	188616		30	1
8	188659		10	1
9	188659		10	1
10	188659		10	1
11	188659		10	1
12	188659		10	1
13	188659		10	1
Sheet6

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT((D3:D13=1)*(IFERROR(C3:C13/COUNTIFS(D3:D13,1,A3:A13,A3:A13),0)))
D3:D13	D3	=SUBTOTAL(103,A3)

Fluff.xlsm
	A	B	C	D
1				30
2
3	188612		20	1
4	188612		20	1
8	188659		10	1
9	188659		10	1
10	188659		10	1
11	188659		10	1
12	188659		10	1
13	188659		10	1
Sheet6

Cell Formulas
Range	Formula
D1	D1	=SUMPRODUCT((D3:D13=1)*(IFERROR(C3:C13/COUNTIFS(D3:D13,1,A3:A13,A3:A13),0)))
D3:D4,D8:D13	D3	=SUBTOTAL(103,A3)

 

[liakos]

Orders 2024 Jan.XLSX
	A	B	C	D
1			90	0
2	Shipment Number	Operator	Loading Minutes
3	188612	A	20	1
4	188612	A	20	1
5	188614	B	30	1
6	188614	B	30	1
7	188616	D	30	1
8	188659	C	10	1
9	188659	C	10	1
10	188659	C	10	1
11	188659	C	10	1
12	188659	C	10	1
13	188659	C	10	1
Sheet2

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((C3:C13/COUNTIF(A3:A13,A3:A13)))
D1	D1	=SUMPRODUCT((D3:D13=1)*(IFERROR(C3:C13/COUNTIFS(D3:D13,1,A3:A13,A3:A13),0)))
D3:D13	D3	=SUBTOTAL(103,A3)

 

[Fluff]

Odd, that works for me. Try confirming the formula with Ctrl Shift Enter, rather than just Enter.