Calculating Quantities in One Column By Prices In Another

[chefelf]

I'm trying to get this sheet to add up so that it takes the number of items for each item number, multiplies it by the unit price,

then returns the number for each department with a total that each area is spending.


Below is the sheet I'm working on. I'm just unsure how to go about addint up the column by the price for each area.


\1


Thanks in advance for any help!

[darkyam]

One way is to put this in B22 and drag down:
=SUMPRODUCT(OFFSET($A$5,0,MATCH(A22,$B$3:$Q$3,0),COUNT($A$5:$A$18)),$S$5:$S$18)

[NBVC]

Try something like:

=SUMPRODUCT(($B$3:$Q$3=A22)*($B$5:$Q$18)*($S$5:$S$18))

copied down

[chefelf]

That worked perfectly. Thank you so much!