Forum Discussion
Help with array formula
Hope someone can help me with writing a formula for Microsoft Excel: I have data in the area A3:DP240593. I want the formula to count and sum the number of rows in the area (from 3 to 240953)...
Christian_Godskesen With almost 250,000 rows of data, and multiple criteria to boot, array formulas will probably perform poorly... there will most likely be a noticeable lag when entering the formula, as well as when updating or changing any data within the referenced range. As such, this seems like the perfect scenario for the rarely used DCOUNT function to shine. The only catch is that it requires a criteria range to be setup on the worksheet (with the applicable column headers), similar to that of Advanced Filter. And just like Advanced Filter, formulas can be used in the criteria range, provided they meet the following 3 rules:
- No heading
- The formula must result in TRUE or FALSE
- The formula should reference the first row of the data range.
For example:
DCOUNT with Formula in Criteria Range
As you can see in the screenshot above, the criteria for column D and P were input in cells AJ3 and AK3 respectively, with the criteria for columns AE to AH input as a formula in cell AL3 as follows:
=SUM(COUNTIFS(AE3:AH3, {1,2}))>2
Then, the DCOUNT formula used in cell AN3 was simply:
=DCOUNT(A2:AH240953, AJ2, AJ2:AL3)
Note: the criteria range can be setup anywhere, including on a separate sheet. Please change the headers accordingly to match your table. Cheers!
djclementsHey, thank you! That looks like a possible solution. In the end, though, I decided to break everything down to some simpler formulas that only required a little extra manual work.
