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

Filter works as well, thanks, for the help, I do like the sumproduct version, just because it's alway been my favorite function, which I've overlooked as spend most of my time trying to do stuff with P query and pivot.

Just to add about ctrs shift enter in 365, it can be useful when entering array formulas like the xlookup part used here into a Table, it stops the spill error.

Richard.

Answer 4

You are welcome.