[Formula Help] - Countif department and not blank?

Hi everyone - I'm stuck trying to figure out how to format this question better. I'm working on building a dashboard for some reporting. The dashboard captures metrics from different quarters. (Average number of training hours). I'm trying to figure out how to set up a formula so that empty cells aren't counted as part of the average. I'm hoping you can help me. Here's some example data

Person	Department	Q1 Learning Hours	Q2 Learning Hours
Jane	Finance	10	5
John	Operations	15	8
Bill	Litigation	10	10
Tim	Litigation	20	20
Sam	Litigation		30

In this example, Sam onboarded in Q2 and should not be counted in the average for Q1. I'm currently using this equation:

=SUMIF(Department:Department, "Litigation", [First Quarter]:[First Quarter]) / COUNTIF(Department:Department, "Litigation")

I imagine I need to add "not" & "isblank" to the Countif Section to exclude empty cells in Q1 - but I don't know how to do that. Any help would be appreciated.

Find more posts tagged with

Formulas

Accepted answers

All comments

Isis Taylor

@Sam N This should work for the Average.

=AVERAGEIF(Department:Department, "Litigation", [Q1 Learning Hours]:[Q1 Learning Hours])

jmyzk_cloudsmart_jp

@Sam N

I confirmed that the AVERAGEIF function works. I wasn't aware of this before, but as @Isis Taylor indicated, the function indeed automatically excludes blank values from the average calculation. I tested it using the demo sheet in the attached screenshot, and it works perfectly!

https://app.smartsheet.com/b/publish?EQBCT=857e943cafb44c5cb54897c09044d94c

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.