Sum multiple lookup values

[Matt Knight]

Hopefully someone can help me with this:

I have a spreadsheet giving activity and copst values for medical
procedures and I'm trying to quickly and easily summarise by
department (General Surgery, OBGYN, Trauma & Orthpaedics etc). What I
could do with is a method of looking up the department and summing all
the values in that department.

So I want to lookup the values from Tab1:

Col C Col D Col E Col F Col G
Dept Procedure Code Unit cost Activity
Gen Surgery Surgury1 xxxx £20 5
Gen Surgery Surgury2 xxyz £50 23
Gen Surgery Surgury3 xyza £25 32
Urology Prodedure1 zzzz £10 18 etc etc

So I get a sum of activity (Col G) for each dept. to land in sheet 2

Col A Col B
Dept Activity
Gen Surgery 60
Urology 18+...

If anyone can help, I'd meassively appreciate it (hopefully this all
makes sense). I'm ideally looking for a formula so I can easily
manipulate to lookup other values (plus, my work machine is already
struggling with having 15000 rows without macros!!)

 

[Pete_UK]

With your departments listed in column A of Sheet2, starting in A2,
put this in B2:

=SUMIF(Sheet1!C:C,A2,Sheet1!G:G)

then copy down as required.

Hope this helps.

Pete

 

[Matt Knight]

With your departments listed in column A of Sheet2, starting in A2,
put this in B2:

=SUMIF(Sheet1!C:C,A2,Sheet1!G:G)

then copy down as required.

Hope this helps.

Pete









- Show quoted text -

Brilliant- thought it might be something really simple. D'uh to me it
is then! Thanks alot for your help!

 

[Pete_UK]

You're welcome, Matt - thanks for feeding back.

Pete

 

[Matt Knight]

You're welcome, Matt - thanks for feeding back.

Pete



- Show quoted text -

I think I spoke too soon!

I neglected to mention that the data in Sheet1 is filtered, which I
think is throwing the SUMIF function out of whack. Don't know if
there's something akin to "=SUBTOTALIF(109,Sheet1!C:C,A2,Sheet1!
G:G)" (and I actually double checked to make sure there wasn't such a
function).

I've been playing around with the formula you posted and it's giving
too high a number for column B and then too low a number for column C
(which looksup Column H of Sheet1) Apologies if that sounds really
confusing!

 

[Matt Knight]

I think I spoke too soon!

I neglected to mention that the data in Sheet1 is filtered, which I
think is throwing the SUMIF function out of whack.  Don't know if
there's something akin to "=SUBTOTALIF(109,Sheet1!C:C,A2,Sheet1!
G:G)" (and I actually double checked to make sure there wasn't such a
function).

I've been playing around with the formula you posted and it's giving
too high a number for column B and then too low a number for column C
(which looksup Column H of Sheet1)  Apologies if that sounds really
confusing!

Sorry- Ignore the previous post- figured out why I was getting the
wrong values: all me being an idiot!! (General Surgury appears within
more than 1 subsection where I'm analuysing each subsection
separately).

Thanks again for your help, and apologies for my idiocy!

 

[Matt Knight]

Hmmm- I can't figure out a way to ignore hidden values in this
function- I'm sure there must be a simple add-on to the above formula
to ignore values ommitted from the filter??

 

[Matt Knight]

Hmmm- I can't figure out a way to ignore hidden values in this
function- I'm sure there must be a simple add-on to the above formula
to ignore values ommitted from the filter??

Right- spoke to a colleage of mine who gave me this formula, which
gives me the values I wanted!

=SUMPRODUCT(--('Raw data'!C2:C1689=Summary!A14),SUBTOTAL(9,OFFSET('Raw
data'!G2:G1689,ROW(G2:G1689)-MIN(ROW(G2:G1689)),0,1))),

Where column C contains the label I wanted to lookup, and column G
contains the data to be added.

Hopefully this will be of use to someone else (although I'm not
entirely sure exactly why this works, other than I know that I wanted
SUBTOTAL embedded somewhere!)

Matt