Forum Discussion

Christian_Godskesen's avatar
Christian_Godskesen
Copper Contributor
Feb 09, 2024

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)...
excel
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Feb 10, 2024

Christian_Godskesen 

=(D3:D15=208000)*(P3:P15=1)*(BYROW(AE3:AH15,LAMBDA(x,SUM(COUNTIFS(x,{1.2}))))>=3)

 

This formula checks for each row if all the conditions are met and returns either 1 or 0. The formula can be wrapped into SUM to returns the total number of matching rows.

 

=SUM( (D3:D15=208000)*(P3:P15=1)*(BYROW(AE3:AH15,LAMBDA(x,SUM(COUNTIFS(x,{1.2}))))>=3) )

 

All ranges can be changed according to the actual database and {1.2} can be replaced by other values.

 

Christian_Godskesen's avatar
Christian_Godskesen
Copper Contributor
Feb 12, 2024

OliverScheurich Wow, that is one impressively complex formula. It looks just right. Thank you!

 

One thing, I'm using Excel in Danish. Is there a way to automate translation of the format to different versions of Excel? Because I don't know the Danish names of a few of those functions.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 12, 2024

    Christian_Godskesen 

    You are welcome. I can't translate formulas into Danish but you can open the attached file and then all formulas are displayed in your language. In column B i've added another formula which works in legacy versions of Excel such as Excel 2013 as well. The dynamic array formula in column C only works in Office 365 or Excel for the web.

Resources