How to count only under certain conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-2064995%22%20slang%3D%22en-US%22%3EHow%20to%20count%20only%20under%20certain%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2064995%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3ESo%20I%20have%20a%20table%20with%20multiple%20columns.%26nbsp%3B%20For%20a%20given%20range%20of%20dates%2C%20I%20would%20like%20to%20count%20how%20many%20times%20the%20word%20%22Complete%22%20appears%20in%20the%20next%20column.%3C%2FP%3E%3CP%3EExample%2C%20if%20I%20were%20looking%20between%20the%20dates%2012%2F16%2F20%20and%201%2F1%2F21%2C%20I%20would%20expect%20the%20answer%20to%20be%201%20from%20the%20following%20table.%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%2224.291497975708502%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E%3CP%3EDate%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EFinished%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2227px%22%3E12%2F20%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2227px%22%3E%3CP%3EIncomplete%3C%2FP%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E12%2F21%2F20%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EComplete%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3E1%2F4%2F21%3C%2FTD%3E%3CTD%20width%3D%2225%25%22%20height%3D%2230px%22%3EComplete%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI've%20tried%20using%20if%2C%20count%2C%20and%20countif%20normally%20and%20as%20matrices%20but%20am%20having%20no%20luck.%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2064995%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
Occasional Visitor

Hi!

So I have a table with multiple columns.  For a given range of dates, I would like to count how many times the word "Complete" appears in the next column.

Example, if I were looking between the dates 12/16/20 and 1/1/21, I would expect the answer to be 1 from the following table.

Date

Finished
12/20/20

Incomplete

12/21/20Complete
1/4/21Complete

I've tried using if, count, and countif normally and as matrices but am having no luck. 

Thank you in advance!!

1 Reply

@smjoyce 

That could be as

image.png

with

=COUNTIFS(D5:D7, ">="&DATE(2020,12,16),D5:D7, "<="&DATE(2021,1,1),E5:E7,"Complete")

but better not to hardcode parameters within formula