# Return Value Based on Two Criteria, Filtered by Third

#### JSTallings

##### New Member
I have a table with the following table:
 Student_Name Transfer Community_College Jane Doe Yes No Jane Doe No Yes John Doe No No John Doe No No

<tbody>
</tbody>

Is it possible to add a fourth column that returns a "Yes" or "No" for each unique Jane and John if there is ever a "Yes" in both columns? I would ultimately like to filter a unique list of those students who have transferred.

 Student_Name Transfer Community_College Transfer Jane Doe Yes No Yes Jane Doe No Yes Yes John Doe No No No John Doe No No No Bill No Yes No Bill No Yes No

<tbody>
</tbody>

Longer question:
I am pulling information from a large dataset on our graduates. Every time a student drops out, transfers, or graduates, another row is added with ID#, name, dates, etc. The schools are numbered 1, 2, 3, based on the sequence of the student's enrollment. I am using Power Query successfully to append new data, but obviously nothing is normalized.

The "Yes" and "No" come from a calculated column in PowerPivot that simply returns a "Yes" in column C if a student started in a community college (it is a community college and numbered "1") and a "Yes" in column B if they transferred to a university (it is a university and numbered >1). The larger issue is that each student may have over 14 rows as they move through their degrees.

Can anyone help me in my ignorance? I have a related, normalized table with the students' names, ID numbers and such; since the transfer only happens once, maybe that is a good place for the information rather than repeating it all over the place?

Thank you!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Welcome to MrExcel,

For your first question, this is one formula that should work...
=IF(AND(COUNTIFS([Student_Name],A2,[Transfer],"Yes"),COUNTIFS([Student_Name],A2,[Community_College],"Yes")),"Yes","No")

For your longer question, I'm not sure what you are asking. It would be more efficient to store a value for Transfer and Community College in a normalized table, instead of calculating that value redundantly from your entire dataset. The best approach would depend on considerations like how you would populate that value in your normalized table and whether adding that column to the normalized table is consistent with how your data is organized.

Hey! Thanks very much. That worked perfectly!

For the longer question:
I basically want to do the above, but with a DAX function in PowerPivot so I don't need to build a table in Excel.

Obviously I cannot use cell references, and I know the query will be contextual, so the results will change if I filter either of the three columns.

I think I need to return either an array or a table and run my filters against that, but I'm not sure of the right DAX function.

I have tried the following, but it does not return anything. No errors, just blank.

=CALCULATE(COUNTA(fStudentProgress[Student_Name]), FILTER(fStudentProgress, fStudentProgress[Community_College]="Yes"), FILTER(fStudentProgress, fStudentProgress[Transfer]="Yes")

Replies
9
Views
2K
Replies
11
Views
906
Replies
1
Views
648
Replies
1
Views
576
Replies
6
Views
288

1,196,409
Messages
6,015,104
Members
441,870
Latest member
kojack

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back