Forum Discussion
Match / CountIfs or other?
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)
)
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,The easiest way to suppress the 0s being returned is to use custom cell formatting which uses four different slots separated by semicolons:
Positive;Negative;Zero;Text
In this example below, the custom cell formatting code is 0;-0;;General
Notice how the third slot is skipped - there are two consecutive semicolons. Additionally, Excel will shorten the formatting code by dropping "General" but I've included it for illustrative purposes.
Re: filtering for multiple sheets. It's do-able but will require the data be consolidate with VSTACK using a 3D reference.
