# Countifs, count how many times a dates occurs in a range for a customer

• Hi all,

How can I count how many times a date occurs between two dates in a range for a given customer? I have tried SUMIFS, COUNTIFS but I can't reach my goal.
Attached you will find an example with COUNTIFS but the result is incorrect. Can someone help me?

PS: date= dd/mm/yyyy

## Files

• Cell H15 formula , Drag down

Code
``=SUMPRODUCT((ISNUMBER(SEARCH(\$C\$1:\$C\$10,B15)))*(\$F\$1:\$J\$10>=--"1/11/2022")*(\$F\$1:\$J\$10<=--"30/11/2022"))``
• Hi WK9128,

The formula you suggested works perfectly! Thank you for that.

However, I have some questions.

Why can't the Range (C1:C10) be changed into Range(C1:D10)? When I extend the range to multiple columns, I get an error.
Is there a solution to extend the Range to multiple columns?
If any cell is empty in the search range, the result is wrong (See attachement)

Thanks a lot!

## Files

• Hello,

Just a couple of remarks :

• wk9128 has proposed a Sumproduct function which is already an array formula ... so there is no need for CSE - Ctrl Shift Enter keys in order to enter the formula -
• when designing an Excel spreadsheet, to be effective, there are 7 golden rules :
7 Golden Rules for Spreadsheet Design

Hope this will help

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim

Hello,

Just a couple of remarks :

• wk9128 has proposed a Sumproduct function which is already an array formula ... so there is no need for CSE - Ctrl Shift Enter keys in order to enter the formula -
• when designing an Excel spreadsheet, to be effective, there are 7 golden rules :
7 Golden Rules for Spreadsheet Design

Hope this will help

You are talking about: there is no need for CSE? I didn’t mention or ask anything about CSE?

The designing of the workbook I have put in attach, isn’t a real workbook, it was just created to illustrate the problem I had. So it isn’t representative.

I’m sure that wk9128 can figure out the problem I have and can propose an answer which I would really appreciate.

• Quote

You are talking about: there is no need for CSE? I didn’t mention or ask anything about CSE?

The file you posted ... shows you are using CSE ...

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• A CSE formula in Excel is an array formula that must be entered with control+shift+enter. When a formula is entered with CSE, Excel automatically wraps the formula in "curly braces {}". (I just looked it up, because I'm not that smart 😉)

I can't find any formula in the file I posted with "Curly braces"?

• I can't find any formula in the file I posted with "Curly braces"?

H15 - K17

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hi Carim,

That is strange, in my version of excel there are no formula's with "Curly braces".
My Excel is in another language than Englisch (It is in Dutch).
I'm using Version 365

That is why I didn't understand your posting ... 😉

• Agree ... It is all Dutch to me ...!!!

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!