SUMIF Multi criteria and date range

[Detectiveclem]

Hi

The following COUNTIF formula =COUNTIF(Range,"Key*")+COUNTIF(Range,"Potentially*") works perfectly well.

However I want to add a further criteria which allows me to enter a start date in CELL F4 and an end date in CELL H4. I have tried adding the following formula Daterange,">="&$F$4,Daterange,"<="&$H$4 but have been unable to do it correctly. Daterange contains all the dates in one column.

I would be grateful if someone would help me with the correct formula to allow me to obtain a result based on the date range selected.

Thank you

 

[AlanY]

could you post a sample sample of your data and desired results so that we can have a look

 

[Detectiveclem]

Hi AlanY, I have tried this but it doesn't work!!! =COUNTIFS(Range,"key*",Range,"Potentially*",Daterange,">="&$F$4,Daterange,"<="&$H$4)

Basically I have about 100k rows of data the column I have called Range contains blanks, or the work starting with Key or the word starting with Potentially. I require the total COUNT of the combined cells containing either of these words. This works perfectly well when I use the formula =COUNTIF(Range,"Key*")+COUNTIF(Range,"Potentially*"), but I now need the ability to only count the cells containing either the words mentioned above based upon if they fall within a date range. The start date in CELL F4 and an end date in CELL H4.

I hope this makes the issue clearer.

Thanks

 

[AlanY]

try this modified version of your original formula

=COUNTIFS(Range,"key*",Daterange,">="&F4,Daterange,"<="&H4)+COUNTIFS(Range,"Potentially*",Daterange,">="&F4,Daterange,"<="&H4)

 

[Detectiveclem]

Works perfectly, thank you so much. I have been trying for hours but now I see your formula it all makes sense.

Thanks again

 

[FormR]

Hi, you can also write the formula like this:

=SUM(COUNTIFS(Range,{"key*","Potentially*"},Daterange,">="&F4,Daterange,"<="&H4))

 

[AlanY]

Works perfectly, thank you so much. I have been trying for hours but now I see your formula it all makes sense.

Thanks again

you're welcome

 

[Detectiveclem]

Thank you FormR