Forum Discussion
Match / CountIfs or other?
Is anyone able to help me with a formula that I have been struggling with for some time now. I would like to calculate the value added score across the whole group of learners from their Projected Gr...
If I understand the goal correctly, the objective is finding how many grades (Projected - MinExpected) fall in one of three categories and then summing the + or - for each category.
A 365 solution:
=LET(
MinExpected, XMATCH(L13:L26, DataSheet!A2:A10),
Projected, XMATCH(M13:M26, DataSheet!A2:A10),
diff, IFERROR(Projected - MinExpected, 0),
bucket, SIGN(diff),
Total, LAMBDA(i, SUM(FILTER(diff, bucket = i, 0))),
BYROW({1; 0; -1}, Total)
)
- Thank you all, I appreciated the help massively.
Follow up question, is it possible to copy a whole row to a new spreadsheet on a single cell critiera?
Something like if any cell in A Column has CF222 copy from this sheet to a different sheet.
Or,
If any cells in F Column has '19 and over' then copy it to anther sheet in the same document?
thanks,You can use FILTER.
Here's a basic example where records with "red" in a table's 'color' column are retrieved:
=FILTER(Demo,Demo[Color]="red")Thank you Patrick2788 that works great. When I have used this in my document, it is copying blank cels as 0 into the other sheet. Is there anyway of removing these?
Also, is there a way of using this formula to search across more than one sheet a data row?
Huge thanks,
