Median & Sum of Multiple Columns in Date Range

MJones4372

Hello,

I am attempting to write a formula to display the median of 6 columns, only if they contain data within the last 7 days per the date column. I also need to display the sum of the same 6 columns if they contain data within the last 7 days per the date column.

I've tried what seems to be an endless variety of formulas to try and make this happen and keep running into unparsable or incorrect argument errors.

Does anyone know if this is possible and if so, how to do it?

Thank you,

Miranda

BullandKhmer

Just do a SUMIFS a COUNTIFS and the median is just SUMIFS/COUNTIFS right? its easy.

But it sounds like you are probably using incorrect formula syntax. You haven't given any details so it'd be impossible to say what the issue is.

Paul Newcome

The issue is that you are trying to pull in six columns of data based on a single column which means your range sizes within the same function do not match. One is a single column and the other range is a set of six columns.

My suggestion would be to use two helper columns. One for the sum and one for the median to be output on each individual row (if the date is within the last 7 days).

Sum:

=IF(AND([Date Column]@row>= TODAY(-7), [Date Column]@row<= TODAY()), SUM([1st Column]@row:[Last Column]@row))

Median:

=IF(AND([Date Column]@row>= TODAY(-7), [Date Column]@row<= TODAY()), AVG([1st Column]@row:[Last Column]@row))

Then you can run a basic SUM or AVG function (as appropriate) on these helper columns ignoring the date criteria since the only rows that will have data are ones that already meet the date criteria.