Need to sum values based on set criteria: SUMIF, SUMPRODUCT or something else?

I'm using the following COUNTIF formula to count the number of occurrences of each depts from a list of depts:
=COUNTIF(Sheet5!E:E,A3)

What I need is to get the sum of column DU in Sheet5 for the rows returned from the COUNTIF.

For ex: the COUNTIF returns a 7-hits for the Finance Dept and I want to sum column DU (Sheet5) for those 7 instances.

I've been trying SUMIF and SUMPRODUCT with no luck and need some help.

Unfortunately I'm too close to the trees and need insight from a different view.

Please let me know if some sample data is needed.

Any help is highly appreciated!

Sample data is always useful to see a problem in context, but try this:

=SUMIF(Sheet5!E:E,A3,Sheet5!DU:DU)

Hope this helps.

Pete

Perfecto Pete!

Thanks!

I thought that was tried, but my statement must have been off.

fwiw: I'm a big a believer in sample data as well, but I'm glad you were able to assist without it.

Have a good one!

A quick follow-up, I see how easy it is to get the Average with AVERAGEIF, but what about getting the median?

It doesn't look like =MEDIAN(IF(Sheet5!E:E,A3,Sheet5!DU:DU) works. Am I missing something?

Thanks!

Thanks for the rep.

Regarding your follow-on query, you need to do it as an array* formula, like this:

=MEDIAN(IF(Sheet5!E$2:E$1000=A3,Sheet5!DU$2:DU$1000))

*Note that an array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE), rather than the usual Enter. If you do this correctly, then you will see curly brackets { } around the formula when viewed in the formula bar, but you should not type these yourself. If you need to edit the formula, you must use CSE again.

Note also that I have used a reduced range, as an array formula will use every cell in the range and thus can become a bit sluggish. You can copy the formula down in your usual way(s).

Hope this helps.

Pete