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) that meet these three conditions:
1) The D cell must say '208000'.
2) The P cell must say '1'
3) At least three of the cells AE, AF, AG, and AH must say either '1' or '2'.
All three conditions must be met for a row to be counted in the sum total.
Hope someone can write this formula for me. I tried ChatGPT but return several formulas that all return af sum of 0, which I know is wrong for my spreadsheet, if the formula was correct.
(I do know that I could use the sorting function manually to remove the rows that don't. The problem is that I need to use the same kind of formula with different specific numbers.)
Best wishes
Christian
Could you do some clarifying before I or somebody else tries to answer you.
I want the formula to count and sum the number of rows
Is there a reason why you use both "Count" and "Sum" in that request? If so, what is that reason? If not, may we conclude that what you really need is just a count? Or do you want the sum of one or more of the columns in the rows that meet the criteria?
that meet these three conditions:
1) The D cell must say '208000'.
2) The P cell must say '1'
3) At least three of the cells AE, AF, AG, and AH must say either '1' or '2'.
And can you assure us that all of those figures are legitimately numbers? Or are one or more of them text masquerading as numbers?
Is this a spreadsheet that is shareable? That is, would you be willing and able to post a copy on OneDrive or GoogleDrive with a link here that grants access? Or, probably easier, could you post a copy of a representative section of it, just so formulas could be tested against some actual representative data?
mathetes Thanks for your reply. Yes, your first assumption was correct. I'm very new to using Excel with formulas this complex. And yes, actually, a there was text masquerading as numbers, which turned out to be part my problem!
In the end, I decided to just break everything down into three much simpler formulas that did the same thing and with minimal extra manual work by me. So thank for useful the useful hint on text and your precision.
=(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.
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.
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.
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}))>2Then, 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.
