Average count by department of non-blank values

Nick Korna

Hi,

I have a sheet (produced from a template) for which the relevant fields look like this:

The brown columns are just a visual aid to show where the weekends fell in this month (October), but potentially could have values in during other months.

What I'm trying to accomplish is a rounded (up) average count of non-blank values by department where the home department is "1". For example:

Expected values:

Maintenance: 1

Goods In: 2

Putaway: 1

Exports: 2

Next Day: 12

However, I can't work out how to get the COUNTIFS portion of a formula to display anything other than #INCORRECT ARGUEMENT SET when putting in multiple criteria.

Using, for example, Next Day:

=COUNTIFS(Department:Department, "Next Day", [Home department checker]:[Home department checker], "1", [1st]:[31st],

I can get this to work in individual constituent parts (or both the department & checker together), but what seems to break it is having the [1st]:[31st] range in with the rest.

Can someone point me in the right direction of where I'm going wrong?

Paul Newcome

The issue is that ranges within the same function must all be of the same size (in this case the same number of columns wide).

What you will need to do is create a helper column with a formula that calculates on each row and then reference this helper column in your formula with the Department and Checker columns.

Nick Korna

Hi @Paul Newcome,

Thanks for pointing out the limitation. I had tried using a small novella of a formula which would work things out, but the last few days can't fit into the summary box due to length (it runs out of room during the 29th). This goes along the lines of:

=AVG(IF(COUNTIFS(Department:Department, "Next Day", [1st]:[1st], >0) > 0, COUNTIFS(Department:Department, "Next Day", [1st]:[1st], >0), ""),IF(COUNTIFS(Department:Department, "Next Day", [2nd]:[2nd], >0) > 0, COUNTIFS(Department:Department, "Next Day", [2nd]:[2nd], >0), ""),IF(COUNTIFS(Department:Department, "Next Day", [3rd]:[3rd], >0) > 0, COUNTIFS(Department:Department, "Next Day", [3rd]:[3rd], >0), "") etc.

Any alternative I could use to streamline this?

Paul Newcome

Yes. I would put a helper column on the sheet and have the COUNTIFS there on every row. Then in the sheet summary field you can use the AVG function on the helper column.