COUNTIFS help

thebenjames

New Member
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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 rangeNumber 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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

thebenjames

New Member
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

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
Joined
Dec 30, 2008
Messages
12,639
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 12, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,151
Members
417,010
Latest member
jnuss03

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?

Disable AdBlock

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
Top