Help with Calculating Median per Group per Year

[TheWes]

Hi everyone,

Hopefully a quick question:

I have the following setup in Excel:

• Sheet 1:
  
  • Column A (A1 to A34): Groups 1 to 34
  • Columns B to J (B1 to J1): Years 2017 to 2024
• Sheet 2:
  
  • The groups are subdivided into articles, with corresponding numbers/values for each year.
  • Each group has multiple articles, and the values for each year vary.

What I need:

• I want to calculate the median value for each group for each year. (based on the numbers for different articles per year)
• The challenge is that each group has multiple entries and varying values across years, so I can’t simply sum them.

I was considering using a SUMIF-type function but can’t seem to make it work. Any suggestions for achieving this?

Thanks in advance!

 

[Kaper]

You may still use MEDIAN formula, but if you have Excel 2016 (as shown in your profile) you will have to use Array commitment (Ctrl+Shift+Enter).
The formula (for a setup like on a screenshot) is:

=MEDIAN(IF(($A$2:$A$10=$G2)*($B$2:$B$10>=DATE(H$1,1,1))*($B$2:$B$10<=DATE(H$1,12,31)),$D$2:$D$10,""))

SUMIF is obviously wrong for median. it would be OK for mean. For median you could use LARGE (or SMALL).

BTW. It would be easier to answer if you'd provide a sample data as xl2bb pasted parts of both sheets