Modify formula to add another criteria -- ISSUE

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
434
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hello - I have an existing formula that works as intended but I am having an issue trying to in-cooperate another criteria to check if the data matches to today's date. I have the date in Cell A3 but i get a null. I can provide XLBB to assist didn't know if there was a quick thing I am just excluding

Existing formula works
=SUMPRODUCT(('Ns'!$A$6:$A$30000<>"")/COUNTIFS('Ns'!$A$6:$A$30000,'Ns'!$A$6:$A$30000&""))

Modification for date criteria
=SUMPRODUCT(('Ns'!$A$6:$A$30000<>"")/COUNTIFS('Ns'!$A$6:$A$30000,'Ns'!$A$6:$A$30000&"",'Ns'!$B$6:$B$30000,A3))
Result: #DIV/0!

B6:B30000 will have the date. A3 = today()
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
434
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
here is the XLBB. Anyone able to assist?

test2.xlsm
ABC
19/30/2020
2
3Total19
4DESIRED16
5
6TypeSubmission Date
7AAA9/30/2020
8BBB9/30/2020
9CCC9/30/2020
10DDD9/30/2020
11EEE9/30/2020
12FFF9/30/2020
13GGG9/30/2020
14HHH9/30/2020
15III9/30/2020
16JJJ9/30/2020
17KKK9/30/2020
18LLL9/30/2020
19MMM9/30/2020
20NNN9/30/2020
21OOO9/30/2020
22PPP9/30/2020
23ZZZ9/29/2020Not current Day
24XXX9/29/2020Not current Day
25YYY9/29/2020Not current Day
26OOO9/30/2020Duplicate
27OOO9/30/2020Duplicate
28OOO9/30/2020Duplicate
29PPP9/30/2020Duplicate
Ns
Cell Formulas
RangeFormula
B1B1=TODAY()
B3B3=SUMPRODUCT((Ns!$A$7:$A$3001<>"")/COUNTIFS(Ns!$A$7:$A$3001,Ns!$A$7:$A$3001&""))
 

Watch MrExcel Video

Forum statistics

Threads
1,132,785
Messages
5,655,286
Members
418,185
Latest member
snoogz2

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