Dynamic COUNTIFS Formula

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
497
Office Version
  1. 2013
Platform
  1. Windows
I'm trying to build out a dynamic Countif formula, so that as additional records are added, results are counted. The underlying formula works for a static range, but I need to be able to count new records as they're added, without having to manually update the range references.

Excel Formula:
=COUNTIFS('EN3485 Tracking'!U3,"Pending Submission of Changes - Late",'EN3485 Tracking'!B3,"Letters")
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,307
This will count the entire column where both conditions are true. The COUNTIFS function only counts to the last used row on the sheet.

Excel Formula:
=COUNTIFS('EN3485 Tracking'!U:U,"Pending Submission of Changes - Late",'EN3485 Tracking'!B:B,"Letters")
 

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
497
Office Version
  1. 2013
Platform
  1. Windows
This will count the entire column where both conditions are true. The COUNTIFS function only counts to the last used row on the sheet.

Excel Formula:
=COUNTIFS('EN3485 Tracking'!U:U,"Pending Submission of Changes - Late",'EN3485 Tracking'!B:B,"Letters")
I tried that earlier, but I must've left the colon out. SMH. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,170
Members
410,775
Latest member
alal1030
Top