Using Unique,Filter & ChooseCols and to see if I can use SUMIF with it?

[Peter Davison]

I have this unique Filter and Choosecols formula below
=UNIQUE(FILTER(CHOOSECOLS('Data Import Store Product'!$D$3:$AD$100000,1,2,3,4,5,6,7,8,9,10,11),'Data Import Store Product'!$D$3:$D$100000<>""))
Is it possible to add to this formula to sum columns like 8,9,10,11 (which are values) but based on the unique criteria from Column 1 (D)

 

[Peter Davison]

My Column D is by product so there may be 100 rows with the same Sub Cat Number (e.g. 14), then it goes onto the next product etc.
I was looking for the answer to sum up all products that have 14 and so on so it ends up on my answer section with only unique sub cats like the example above.
Is that possible?

 

[Peter Davison]

This is an example of the sheet it is looking at -

 

[RoryA]

Your UNIQUE function is looking at columns D:N, so all those rows will be returned individually, and that will lead to repetition of your criteria.

 

[RoryA]

Try something like:

=LET(
rng,FILTER('Data Import Store Product'!$D$3:$N$100000,'Data Import Store Product'!$D$3:$D$100000<>""),
critRng, CHOOSECOLS(rng,1),
uCrit,UNIQUE(critRng),
sumRng, CHOOSECOLS(rng,8,9,10,11),
BYROW(ucrit,lambda(r,SUM(IF(critRng=r,sumRng)))))

 

[Peter Davison]

Okay I understand.
I am bringing in the Unique part from Columns D to J then to achieve the sum for each sub cat I am using sumifs with criteria.
I was only wondering if I could achieve it in one formula.
Thanks for helping me with the understanding.
Appreciate your time.

 

[Peter Davison]

Sorry just had a crossed reply each. Let me have a look at your suggestion

 

[Peter Davison]

That is adding up all four columns 8,9,10,11 together instead of individually.
Can the unique part of Columns G to L come in before the sum up columns?

 

[RoryA]

I don't follow any of that.
If you are only using col D as criteria, what do cols G:L have to do with anything?
What output are you expecting? Need to see sample data and sample output at this point, please.

 

[Peter Davison]

This is what the output looks like
Column D is the criteria for the Sum
Columns E to I are associated information and are not part of any analysis
Columns J to M are the columns to be summed individually based on the criteria Column D

Hope that makes sense?
Thank you again for all this help.

 

[RoryA]

Columns J to M are the columns to be summed individually

What exactly does that mean? You expect 4 results?