Pulling two different dates with COUNTIF

Cassidy Tackett

=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

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?