Forum Discussion
Solved
Need to use COUNTIFS with OFFSET and COUNTA to create a dynamic range within COUNTIFS
I am using the following formula, =COUNTIFS($D$7:$D$68,L9, $F$7:$F$68,">0") within the attached sheet. The two ranges need to be dynamic as thousands of rows will be added in time as my data grows. I...
It should work. See the attached, slightly modified version.
https://www.dropbox.com/scl/fi/2tt16cw5s28lrbiqn6tp2/PDcalcs221-01_2023_02-04.xlsx?dl=0&rlkey=d3luwdtmijcc1amfb2wz80262
It should work. See the attached, slightly modified version.
I plugged your revision in and it worked perfectly! Thanks again Hans!
The final formula...
=COUNTIFS(OFFSET($D$7, 0, 0, COUNTA($D$7:$D$100000), 1), L9, OFFSET($F$7, 0, 0, COUNTA($D$7:$D$100000), 1), ">0")
For anyone else that is interested in what Hans came up with that worked.