Excel Array formula

[deveraux]

Hey guys,

I've been trying to learn more about array formulas (can't say I'm any good at them). Anyhow, I've been trying to essentially solve the following problem:

Say you have a database such as the following:

Item     Part      Price
i7       CPU       300
Phenom   CPU       200
5870     GFX       250
GTX295   GFX       300

Items bought
i7
5870
i7
GTX295

Basically, I need to figure out how to write a formula to essentially determine how much money is spent for say the CPU part or the GFX part. So, if I parse the value 'CPU' to the formula, it should return 600 (2x i7 bought). If I parse the value 'GFX', it should return 550 (250 + 300).

I know how to do this in VBA / custom function. But I'm trying to figure out how to do this using only array formulas etc. Any idea how I might go about doing this?

I have to admit, array formulas are my achilles heel. Anyway, any help on this would be greatly appreciated. Thank you!

PS: A little caveat, I do not wish to add any columns/rows etc to provide more info (I know it can be done if you add another column on the 'Items bought' list to lookup the part type). I'm just trying to figure out if this is possible as part of my learning.

EDIT: Fixing table formatting

 

[Binky]

You could use sumproduct (which isnt an array), but I'm not seeing a way to do this in a single cell in the way you want it formatted.

If your data table shown is in cells A1-c4, and the "items bought" part is in A6-A10, then these formulas copied down through B7-B10 will pull the data you want from the table.

=SUMPRODUCT(($A$1:$C$4=A7)*($C$1:$C$4))
=SUMPRODUCT(($A$1:$C$4=A8)*($C$1:$C$4))
=SUMPRODUCT(($A$1:$C$4=A9)*($C$1:$C$4))
=SUMPRODUCT(($A$1:$C$4=A10)*($C$1:$C$4))

I don't see an elegant way to make this happen in a single cell.


Edit: Here's an array formula that works. I think this is how you were thinking it would work. It's a clunky formula because you have to point at each of the "items bought" individually.

=SUM((A1:A4=A7)*(C1:C4))+SUM((A1:A4=A8)*(C1:C4))+SUM((A1:A4=A9)*(C1:C4))+SUM((A1:A4=A10)*(C1:C4))

 

[deveraux]

Thanks for the reply. Your single cell solution works, but as it requires knowing before hand the number of items bought and hard-coding it, I assume it may not be generalized into a formula that works with any number of line items.

Still, thank you very much for taking a stab at it.