counting rows without use of filter() function

ikokidko

New Member
Joined
Jan 16, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I'm using Excel 2016. I can easily do what I need to with a filter function, but that isn't available to me in 2016.

In column A, I have alphanumeric strings.
In column C, I have dates.

For each row, I would like to check and see if any other row both has a value EQUAL to the one in column A, and is also GREATER than the date in column C. It would be nice if the formula returned the number of results, but I'd be fine with a TRUE if there are any at all, and a FALSE if not.

I can post some sample data but I'm new here, not sure how to make the pretty snippets of spreadsheets I see... so sorry for the plain table. And thanks in advance for any help!
b10000999
6/6/2014​
1/9/2018​
b10001207
6/6/2014​
4/20/2018​
b10001335
6/6/2014​
1/27/2018​
b10001347
6/6/2014​
11/29/2017​
b10001384
6/6/2014​
2/11/2017​
b10001438
6/6/2014​
11/7/2019​
b10001463
6/6/2014​
6/14/2018​
b10001487
6/6/2014​
6/9/2020​
b10001487
6/6/2014​
6/21/2019​
b10001517
6/6/2014​
6/29/2018​
b10001542
6/6/2014​
5/25/2018​
b10001542
6/6/2014​
6/8/2020​
b10001840
6/6/2014​
11/9/2019​
b10001888
6/6/2014​
6/8/2020​
b10002133
6/6/2014​
7/17/2019​
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,591
Office Version
  1. 365
Platform
  1. Windows
not sure how to make the pretty snippets of spreadsheets I see
Those pretty snippets are made with the XL2BB add in, you can click the link in my signature block below for more details.

Looking at your question, is this what you need?

Book1 (version 2).xlsb
ABCD
1b1000099906/06/201409/01/20180
2b1000120706/06/201420/04/20180
3b1000133506/06/201427/01/20180
4b1000134706/06/201429/11/20170
5b1000138406/06/201411/02/20170
6b1000143806/06/201407/11/20190
7b1000146306/06/201414/06/20180
8b1000148706/06/201409/06/20200
9b1000148706/06/201421/06/20191
10b1000151706/06/201429/06/20180
11b1000154206/06/201425/05/20181
12b1000154206/06/201408/06/20200
13b1000184006/06/201409/11/20190
14b1000188806/06/201408/06/20200
15b1000213306/06/201417/07/20190
Sheet3
Cell Formulas
RangeFormula
D1:D15D1=COUNTIFS(A:A,A1,C:C,">"&C1)
 

ikokidko

New Member
Joined
Jan 16, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Yes, that is exactly it, and I was so close! I"m not quite sure I understand the syntax you used thougha and I'd like to learn. I tried almost exactly what you had, but my formula looked like this:

COUNTIFS(A:A,A1,C:C,">C1")

Can you explain why the greater than is isolated, and the ampersand is used?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,591
Office Version
  1. 365
Platform
  1. Windows
Sure, due to the way that COUNTIFS and other similarly named functions work in the background, the greater than, less than, or equal symbols must be enclosed in double quotes.
Because the criteria is in a cell, the cell address must be outside of the double quotes, if it is inside the quotes then it is a text string, not a cell address. The ampersand is used to join (concatenate) the symbol and the cell together.
 

ikokidko

New Member
Joined
Jan 16, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Sure, due to the way that COUNTIFS and other similarly named functions work in the background, the greater than, less than, or equal symbols must be enclosed in double quotes.
Because the criteria is in a cell, the cell address must be outside of the double quotes, if it is inside the quotes then it is a text string, not a cell address. The ampersand is used to join (concatenate) the symbol and the cell together.
Of course... that makes sense now. Thank you for the solution and the explaination! Happy Saturday.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,301
Messages
5,635,408
Members
416,856
Latest member
silentir

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