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)

 

[Domenic]

Your formula returns an array. However, SUMIF only accepts a range for its first argument, the criteria range. Plus it looks like you want to sum four columns based on the criteria for the first column. Therefore, try something like this...

=LET(
     rng,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<>"")),
     critRng, CHOOSECOLS(rng,1),
     crit, A2,
     sumRng, CHOOSECOLS(rng,8,9,10,11),
     SUM(IF(critRng=crit,sumRng)))

...where A2 contains the criteria.

Hope this helps!

 

[Peter Davison]

Sorry I'm not sure I understand what Cell A2 is referring to?
The formula I have been using above sits in Cell G12
If you can advise.
Regards

 

[Domenic]

You mentioned that you wanted to use SUMIF, which suggests you want to sum a range based on some criteria. So, in my example, I've assumed that you want to sum the values in Columns 8, 9, 10, and 11, where the corresponding value in Column 1 is equal to the value in A2.

However, if this is not what you want, I would suggest that you post some sample data, along with the expected results.

 

[Peter Davison]

I see what you are doing now.
After I add in the cell to replace A2
The formula looks like this -
=LET(
rng,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<>"")),
critRng, CHOOSECOLS(rng,1),
crit, 'Data Import Store Product'!D3,
sumRng, CHOOSECOLS(rng,8,9,10,11),
SUM(IF(critRng=crit,sumRng)))
What this does is just put the totals of columns 8,9,10,11 into my starting cell of G12 without the unique spill etc.

My column headings are -

So I want Columns 1,2,3,4,5,6,7 to spill from the unique formula with columns 8,9,10,11 summing up.
Is this possible? Maybe use VStack to get the header in (Row 11) with the data starting at Row 12.
Sorry I cannot give you a file. Unfortunately the data is client sensitive and links to several pieces of info?
Thank you for your time and I'm hoping you can help

 

[RoryA]

Untested but I think you mean something like:

=LET(
rng,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<>"")),
critRng, CHOOSECOLS(rng,1),
sumRng, CHOOSECOLS(rng,8,9,10,11),
BYROW(critrng,lambda(r,SUM(IF(critRng=r,sumRng)))))

 

[Peter Davison]

Thank you for assisting.
The above formula just results in a spill like below -

I was looking for a result like below (This is manually made.

 

[RoryA]

I'm guessing you have repeating values in column D on the Data Import Store Product sheet then?

 

[Peter Davison]

That is correct

 

[RoryA]

Then you will get repeating values, since you are only using column D as the criteria for the SUM.