# Match / CountIfs or other?

Copper Contributor

# 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 Grades (Column M) against their Minimum Expected grade (Column L). I would like to see how many of them are above target Cell V5, how many are on target V6 and below target V7. Heres the tricky bit, the grades values are F, P, HP, M, HM, D, HD as seen on the DataSheet. These presented Fail, Pass, High Pass, Merit, High Merit, Distinction, HighDistinction.

Any help would be greatly appreciated.

/

12 Replies

# Re: Match / CountIfs or other?

Since you've already rendered the names anonymous, could you go the next step toward helping us help you by posting not an image but the actual file. Put it on OneDrive or GoogleDrive with a link pasted here that grants access to it.

# Re: Match / CountIfs or other?

Thank you, I have attached it to the message above now.

# Re: Match / CountIfs or other?

See the attached version.

I defined a named range Grades on DataSheet and used that in the formulas.

# Re: Match / CountIfs or other?

Thank you, that looks great and completely makes sense. Would there be a way to calculate the score as a total; for example a learner who has projected grade as P but the Minimum Expected grade should be HM that would be -2 points. Another student who has projected grade as M and Minimum Expected grade a HP this would be +1.

Try

# Re: 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)
)``````

# Re: Match / CountIfs or other?

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,

# Re: Match / CountIfs or other?

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,

# Re: Match / CountIfs or other?

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")``

# Re: Match / CountIfs or other?

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,

# Re: Match / CountIfs or other?

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.

# Re: Match / CountIfs or other?

Another option is to return empty strings instead of blanks

``````=LET(
f, FILTER(Demo, Demo[Color] = "red"),
SUBSTITUTE(f, "", "")
)``````