# COUNTIFS help

#### thebenjames

##### New Member
Unsure if using COUNTIFs is the right way as I've read SUMPRODUCT may be a better solution, but here goes..

I have 2 tables of data, one of which contains some client information including a date (column Q) and a postcode (column K), and another which contains a very long list of postcodes and whether they are considered 'dangerous'. I need to count how many of the clients in the first sheet have a date which falls between a range (as below), but only if their postcode is included in the 'dangerous' list..

 Date range Number of clients 01/08/2017-31/07/2018 01/08/2018-31/07/2019 01/08/2019-31/07/2020 01/08/2020-31/07/2021

Please let me know if you need any more information

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### steve the fish

##### Well-known Member
Keeping the date ranges like that isnt going to help you whatever formula you use. Split them into Date from and Date to columns. Is that possible else your formula will need to be more complicated?

#### jasonb75

##### Well-known Member
You would need to separate your date ranges into two columns, start date in one, end date in the other. Then you could do it with something like this:-
Book1
ABC
1Date fromDate toNumber of clients
201/08/201731/07/20180
301/08/201831/07/20190
401/08/201931/07/20201
501/08/202031/07/20210
Result
Cell Formulas
RangeFormula
C2:C5C2=SUMPRODUCT(COUNTIFS(Client!\$Q\$2:\$Q\$20,">="&\$A2,Client!\$Q\$2:\$Q\$20,"<="&\$B2,Client!\$K\$2:\$K\$20,TRANSPOSE(List!\$A\$2:\$A\$10)))

As you are using Excel 2019, you will need to array confirm the formula with Ctrl Shift Enter.
I've used a sheet name of Client to identify the client data in the formula and List for the list of postcodes. Use either limited or dynamic arrays, this will not be efficient with 2 columns.

The result of 1 is a random date with some text entered as a postcode to validate the syntax of the formula.

#### thebenjames

##### New Member
Thank you for taking the time to respond! I've tried to implement your solution but it's not returned anything, despite the fact I know there are meant to be results. I have replaced the references in your formula with the sheets/locations in mine and have the following;

=SUMPRODUCT(COUNTIFS(Table1[Client date],">="&'date lookup'!A6,Table1[Client date],"<="&'date lookup'!B6,Table1[Client postcode],TRANSPOSE('Dangerous postcode lookup.xlsx'!Table1[Pst_POSTCODE])))

Would the fact that I am using tables to reference the start/end dates be causing an issue? Or the fact that the 'dangerous' postcodes are on another workbook entirely (this is due to the file being enormous)?

#### thebenjames

##### New Member

Ignore me! It did work.. it just took an eternity for my potato work laptop to actually process it because the postcode list is so big. Thank you kind sir!

#### jasonb75

##### Well-known Member
it just took an eternity for my potato work laptop
You're advanced compared to some companies ?

I just realised that I left in one function which is not necessary, given the slow calculation that you mention, removing it might make some difference. Other than that you would need to use a vba alternative to speed it up (not something I'm familiar with, just know it can be done).
Excel Formula:
``=SUMPRODUCT(COUNTIFS(Table1[Client date],">="&'date lookup'!A6,Table1[Client date],"<="&'date lookup'!B6,Table1[Client postcode],'Dangerous postcode lookup.xlsx'!Table1[Pst_POSTCODE]))``
Also, I notice that your postcode table is in a different workbook, the formula will not give you a result if that workbook is closed.

#### thebenjames

##### New Member

Haha! Don’t let them hear you say that, they’ll never approve my request for one with more than 8gb of RAM

Which function should I remove?

Now that you mention that, it may have just worked when I opened the postcode workbook. I was going to originally keep it within the same workbook, but it’s about 2.5million rows long.. and the workbook I’m creating is designed to be a template for people to use, and having the file be >50mb is not going to go down well! Perhaps I’ll have to reconsider how I go about it. Thank you though!!

#### jasonb75

##### Well-known Member
Which function should I remove?
The TRANSPOSE function (I've already done it for you in my last reply).
but it’s about 2.5million rows long..
Do we drop one digit or two from that estimate? The limit on a single sheet is just over 1 million rows, with large data volumes nothing will be quick. You could even be getting to the stage where excel may not be the ideal choice.

#### thebenjames

##### New Member
Aaahh sorry! I was viewing it on my phone and it didn’t show me the entire formula - I’ll have a proper look at it when I get back to my laptop, thank you so much.

Well technically it’s 5 tabs/sheets with about 500,000 on each one, each referring to a different calendar year as the postcodes are updated each year. I managed to manipulate the formula to work regardless as I just changed the reference on my main sheet.. but it’s still a substantial amount of data. You may be correct about Excel not being the best solution!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,414
Messages
5,837,100
Members
430,477
Latest member
roseinsydney

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back