Multiplying arrays of different sized arrays

Question

Hi,

I'd like to multiply different sized array based on a condition, I've made some attempts using switch,

SUMPRODUCT(SWITCH(Table1[Item],"a",2,"b",3,"c",5,"d",6),Table1[Sales])

But this is not ideal and also I'd like to get the result for each Item, a b c and d .

I'm looking to get a total for each item, I was thinking if I could wrap the above in a IF statement,

IF( X ="a" , Sumproduct, if = "b" etc. , but the formula would still be returning the full amount.

The closest I've got so far is but the array { litems} doesn't seem to work, thought it might, so works fine for each 'a' but then I still need to sum the group?

IF(G4= {"a","b","c","d"},H4*SWITCH(G4,"a",2,"c",5,"b",3,"d",6))

could do with some fresh ideas.

Richard

Answer 1

Hi,

In cell L4, enter this formula and drag down

=SUM(FILTER($H$4:$H$16,$G$4:$G$16=J4,0)*K4)

Hope this helps.

Answer 2

In the screenshot below, the formula in N4 is

=SUMPRODUCT(Table1[Sales]*(Table1[Item]=M4),XLOOKUP(Table1[Item],Table2[Product],Table2[Price]))

If you don't have Microsoft 365 or Office 2021, you may have to confirm the formula with Ctrl+Shift+Enter.

Answer 3

I haven't tried it yet but will, I did come up with ;

SUMIF(Table1[Item],B3,Table1[Sales])*SWITCH(B3,"a",2,"b",3,"c",5,"d",6)

Which isn't ideal but does work. Just to add I have changed the 'souce' numbers.

Richard

Answer 4

Works brilliant, will try filter method,

I did change the Boolean part to a double negative rather than multiplication, I've just always --( ,

also, in the xlookup I've not used an array as the lookup value so will have to look into that a bit more. One last thing, ( office 365) , if you use ctrl. shft enter on an array this now stops the spill and just gives the 1st result in a single cell which can be useful.