Pulling two different dates with COUNTIF

=COUNTIFS({Nominee's Work Location}, "Italy", {Award Type}, "Gift Card", {Authorized Date}, ">=DATE(2024, 12, 1)", {Authorized Date}, "<=DATE(2024, 12, 31)", {Authorized Date}, <>"") + COUNTIFS({Nominee's Work Location}, "Italy", {Award Type}, "<>Gift Card", {Payroll Date}, ">=DATE(2024, 12, 1)", {Payroll Date}, "<=DATE(2024, 12, 31)", {Payroll Date}, <>"")

I am stuck on this problem and would love to know how it can be fixed! I did various videos and cannot seem to crack it.

I am referencing by Location, Per Month. I need to know how many gift cards were authorized in Italy in the month of December 2024- For other awards, I need to know the payroll date for that same month. I am referencing from another sheet, and the sheet is corresponding to my formulas correctly but not giving correct data. Also, blank cells need to be ignored so I do not get an invalid response

Find more posts tagged with

Accepted answers

All comments

DKazatsky2

Hi @Cassidy Tackett,

The first thing to change is removing the quotes from the DATE sections. So the formula would become the following.

=COUNTIFS({Nominee's Work Location}, "Italy", {Award Type}, "Gift Card", {Authorized Date}, >=DATE(2024, 12, 1), {Authorized Date}, <=DATE(2024, 12, 31), {Authorized Date}, <>"") + COUNTIFS({Nominee's Work Location}, "Italy", {Award Type}, "<>Gift Card", {Payroll Date}, >=DATE(2024, 12, 1), {Payroll Date}, <=DATE(2024, 12, 31), {Payroll Date}, <>"")

Let's start with that tweak and go from there.

Hope this helps,

Dave

Paul Newcome

Lets try cleaning up some those quotes first and see what that does…

=COUNTIFS({Nominee's Work Location}, "Italy", {Award Type}, "Gift Card", {Authorized Date}, >=DATE(2024, 12, 1), {Authorized Date}, <=DATE(2024, 12, 31), {Authorized Date}, <>"") + COUNTIFS({Nominee's Work Location}, "Italy", {Award Type}, <>"Gift Card", {Payroll Date}, >=DATE(2024, 12, 1), {Payroll Date}, <=DATE(2024, 12, 31), {Payroll Date}, <>"")

Cassidy Tackett

Hmm.. That's not working.. I tried to break the formula up to see if any numbers formulate and I'm getting 0...

Paul Newcome

Ok. Try a series of COUNTIFS that only have a single range/criteria set. Do one at a time for each of your ranges. Which ones have counts and which ones have zeros?

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.