Formula to Count Unique Values in Column based on Date Ranges in Another

jonnycuest

New Member
Joined
Nov 6, 2017
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking to count unique values in 1 column with criteria that involves date ranges from TODAY()-30 and TODAY() in another column.

Unique Values in Column D:D
Date Ranges in Column V:V which need to fall between TODAY()-30 and TODAY

I can't figure out how to get both of these working together in one formula.

Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jonnycuest

New Member
Joined
Nov 6, 2017
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have a further question on this formula:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10,(LEFT(B2:B10,10)+0>TODAY()-1)*(LEFT(B2:B10,10)+0<TODAY())))),0)

I am trying to search an entire column like B:B instead of B2:B10. Is that at all possible?
Also, what does the "+0" mean in this equation?

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
You can use whole column references, but they should be avoided as it can bring your workbook to a stop.
The +0 is to convert the text returned by Left to a number
 

jonnycuest

New Member
Joined
Nov 6, 2017
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Thank you,

Could you also tell me what the 10 means in this portion? (B2:B10,10)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try reading up on the function, it tells you what it means. ;)
 

jonnycuest

New Member
Joined
Nov 6, 2017
Messages
22
Office Version
  1. 365
Platform
  1. Windows
I've been googling it and I don't see anywhere that explains that
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you type =Left( into a cell it tells you what it means.
Also I don't know what you searched for but this came up as the first reply when I tried How to use the Excel LEFT function | Exceljet
 

jonnycuest

New Member
Joined
Nov 6, 2017
Messages
22
Office Version
  1. 365
Platform
  1. Windows
So I am still having trouble with this equation:

=IFERROR(ROWS(UNIQUE(FILTER('CF - MM Main'!D:D,(LEFT('CF - MM Main'!V:V,10)+0>TODAY()-1)*(LEFT('CF - MM Main'!V:V,10)+0<TODAY())))),0)

This should return a value of 2 but it is returning a value of 0. Any ideas why?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Looks like you have some of the brackets in the wrong place.
 

Forum statistics

Threads
1,147,732
Messages
5,742,855
Members
423,759
Latest member
meb229

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