COUNTIFF function returning a zero

%3CLINGO-SUB%20id%3D%22lingo-sub-2302100%22%20slang%3D%22en-US%22%3ECOUNTIFF%20function%20returning%20a%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2302100%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20to%20complex%20formulas%20(!)%20and%20looking%20to%20create%20a%20COUNTIF%20to%20track%20the%20number%20of%20leads%20from%20a%20variety%20of%20sources*%20within%20a%20date%20range.%26nbsp%3B%20The%20sources%20are%20in%20a%20drop%20down%20list%20and%20I%20would%20like%20the%20date%20range%20to%20be%20easily%20adjusted%20to%20give%20a%20monthly%20result%20(this%20is%20my%20D6%20for%20start%20date%20and%20F6%20for%20end%20date)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%201st%20argument%20is%20B13%3AB101%20for%20the%20source%20eg%20website%3C%2FP%3E%3CP%3E2nd%20argument%20is%20C13%3AC101%20for%20the%20date%20of%20the%20enquiry%3C%2FP%3E%3CP%3EMy%20formula%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3DCOUNTIFS(%24B13%3A%24B101%2CB111%2C%24C13%3A%24C101%2C%22%26gt%3B%3D%22%26amp%3B%24D6%2C%24C13%3A%24C101%2C%22%26lt%3B%3D%22%26amp%3B%24F6)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20outcomes%20to%20appear%20in%20a%20table%20where%20I%20can%20track%20number%20of%20leads%20from%20website%2C%20social%20media%20etc%20in%20a%20given%20range%20for%20one%20of%20my%20clients%20but%20the%20value%20is%20coming%20out%20as%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20changed%20the%20date%20format%20to%20year%2Cmonth%2Cday%20with%20the%20year%20as%204%20digits%20but%20still%20no%20joy.%26nbsp%3B%20Any%20advice%20would%20be%20massively%20appreciated!%3C%2FP%3E%3CP%3EThanks%20so%20much%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3EJo%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-2302100%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-2302326%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIFF%20function%20returning%20a%20zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2302326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1039506%22%20target%3D%22_blank%22%3E%40UpbeatJo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20formula%20shall%20work%20if%20in%20D6%20and%20F6%20are%20dates%20(actually%20they%20are%20numbers)%2C%20not%20texts%20which%20looks%20like%20dates.%20Format%20doesn't%20matter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi there, 

 

I'm new to complex formulas (!) and looking to create a COUNTIF to track the number of leads from a variety of sources* within a date range.  The sources are in a drop down list and I would like the date range to be easily adjusted to give a monthly result (this is my D6 for start date and F6 for end date)

 

My 1st argument is B13:B101 for the source eg website

2nd argument is C13:C101 for the date of the enquiry

My formula looks like this:

=COUNTIFS($B13:$B101,B111,$C13:$C101,">="&$D6,$C13:$C101,"<="&$F6)

 

I want the outcomes to appear in a table where I can track number of leads from website, social media etc in a given range for one of my clients but the value is coming out as zero.

 

I have changed the date format to year,month,day with the year as 4 digits but still no joy.  Any advice would be massively appreciated!

Thanks so much

Jo

 

 

2 Replies

@UpbeatJo 

In general formula shall work if in D6 and F6 are dates (actually they are numbers), not texts which looks like dates. Format doesn't matter.

I suspect the column with dates actually contains text values rather than dates. Select that column, click Data, Text - to - columns and click Finish, this normally resolves this issue.