SOLVED
Home

COUNTIF Dates within dates plus condition.

%3CLINGO-SUB%20id%3D%22lingo-sub-733727%22%20slang%3D%22en-US%22%3ECOUNTIF%20Dates%20within%20dates%20plus%20condition.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733727%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%C2%B4m%20struggling%20a%20bit%20with%20the%20formula%20to%20calculate%20my%20conditions.%20I%C2%B4ve%20attached%20an%20example%20file%20to%20giv%20an%20understanding.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20conditions%20are%20two%20date%20columns%20(A%20%26amp%3B%20B)%20and%20one%20date%20row%20(H6%3AID6).%20I%20have%20four%20hypothetical%20sales%20persons%20(C6%3AF6)%20whom%20get%20marked%20with%20an%20%22x%22%20(C7%3AF7%20and%20down)%20if%20they%20do%20a%20sale%20within%20the%20given%20date%20range%20in%20my%20columns.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20headache%20is%20how%20to%20calculate%20the%20number%20of%20%22ongoing%20sales%22%20per%20day%20each%20salesman%20have%20(HI%3AID4).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20the%20critera%20of%20an%20%22x%22%20within%20a%20date%20range%20needs%20to%20meet%20that%20the%20dates%20in%20the%20date%20row%20occurs%20during%20the%20range%20and%20then%20calcuate%20the%20number%20of%20occurrences%20of%20%22x%22%20during%20that%20specific%20date.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20that%20has%20some%20valuable%20input%20on%20how%20to%20build%20this%20formula%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3EViktor%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-733727%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735521%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20Dates%20within%20dates%20plus%20condition.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735521%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369832%22%20target%3D%22_blank%22%3E%40viktor2170%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20the%20formula%20in%20H1%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DCOUNTIFS(%24A%248%3A%24A%24334%2C%22%26lt%3B%3D%22%26amp%3BH%246%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24B%248%3A%24B%24334%2C%22%26gt%3B%3D%22%26amp%3BH%246%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%24C%248%3A%24C%24334%2C%22x%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThe%20formulas%20in%20H2%3AH4%20are%20similar%2C%20then%20copied%20across%20columns%20until%20Column%20ID.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736146%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20Dates%20within%20dates%20plus%20condition.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736146%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20this!!!%20This%20solved%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regard%2C%3C%2FP%3E%3CP%3EViktor%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736236%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%20Dates%20within%20dates%20plus%20condition.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736236%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20very%20much%20welcome!%3C%2FLINGO-BODY%3E
viktor2170
New Contributor

Hi, 

 

I´m struggling a bit with the formula to calculate my conditions. I´ve attached an example file to giv an understanding. 

 

My conditions are two date columns (A & B) and one date row (H6:ID6). I have four hypothetical sales persons (C6:F6) whom get marked with an "x" (C7:F7 and down) if they do a sale within the given date range in my columns. 

 

My headache is how to calculate the number of "ongoing sales" per day each salesman have (HI:ID4). 

 

So the critera of an "x" within a date range needs to meet that the dates in the date row occurs during the range and then calcuate the number of occurrences of "x" during that specific date. 

 

Anyone that has some valuable input on how to build this formula? 

 

Kind regards,

Viktor 

 

 

3 Replies
Solution

@viktor2170 

In the attached file, the formula in H1 is: 

=COUNTIFS($A$8:$A$334,"<="&H$6,
$B$8:$B$334,">="&H$6,
$C$8:$C$334,"x")

The formulas in H2:H4 are similar, then copied across columns until Column ID.

@Twifoo 

 

Thank you so much for this!!! This solved the problem.

 

Kind regard,

Viktor 

You’re very much welcome!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies