Sum unique values with multiple criteria

[Milos]

Hey all,

I am trying to create a formula in the that only sums the first unique value in column B but I need this sum to include a secondary grouping criteria which is column A (so only sum unique values for the Apple ground and then only sum unique values for orange and so on). I am not quite sure how to add in the the additional logic. I have been testing a combination of SUMPRODUCT, SUMIFS and UNIQUE to no avail.

I have the formula to sum all unique values for all cells using this formula =SUMPRODUCT(1/COUNTIFS(B2$:B$9,B$2:B$9&""),B$2:B$9). This currently gives me a value 381.9 for all cells in C2:C9. I need add a secondary criteria to only sum unique values when column A is the same number which would result in multiple values (361.9, 12 and 8) as shown below.

Can anybody provide a helpful solution to amend the SUMPRODUCT formula?

Thanks,
Milos

 

[GraH]

First attempt, though I believe a single cell formula is possible using LET/LAMBDA combo.

Book1
	A	B	C	D
1	Name	Productivity	Sum Unique Productivity	Helper
2	Apple	246,3	361,9	1
3	Apple	30,6	361,9	1
4	Apple	246,3	361,9	2
5	Apple	85	361,9	1
6	Orange	1	12	1
7	Orange	4	12	1
8	Orange	7	12	1
9	Banana	8	8	1
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=SUMIFS(B2:B9,A2:A9,A2:A9,D2:D9,1)
D2:D9	D2	=SUM(--EXACT(A2&B2,$A$2:A2&$B$2:B2))
Dynamic array formulas.

 

[Peter_SSs]

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’).

I have been testing a combination of SUMPRODUCT, SUMIFS and UNIQUE to no avail.

What about this?

22 06 01.xlsm
	A	B	C
2	Apple	246.3	361.9
3	Apple	30.6	361.9
4	Apple	246.3	361.9
5	Apple	85	361.9
6	Orange	1	12
7	Orange	4	12
8	Orange	7	12
9	Banana	8	8
Sum Unique

Cell Formulas
Range	Formula
C2:C9	C2	=SUM(UNIQUE(FILTER(B$2:B$9,A$2:A$9=A2)))

 

[Soledad]

The array formula used for all versions, in C2 then copied down

=SUM(($A$2:$A$9=A2)*$B$2:$B$9/MMULT(($A$2:$A$9=TRANSPOSE($A$2:$A$9))*($B$2:$B$9=TRANSPOSE($B$2:$B$9)),ROW($A$2:$A$9)^0))

 

[Milos]

Thank you very much for all of your responses. I have also updated my account details.

I tested all of the responses and ended up settling on you sum unique filter approach Peter. Mostly due to the fact that it was easier for me to figure out how to add in a second filter criteria (which I had omitted for simplicity).

Final formula being:
=SUM(UNIQUE(FILTER(C$2:C$9,(A$2:A$9=A2)*(B$2:B$9=B2))))

Cheers,
Milos

 

[Peter_SSs]

I have also updated my account details.

According to Microsoft's Help on the UNIQUE function (& FILTER) it is not available in Excel 2016, so have you updated correctly? Perhaps you actually have Microsoft 365 or Excel 2021?