# Excel 2010 formula help

Hi All,
Excel 2007/2010

Need help with a formula to give me the outcome in 'Column H'
I have put in the 'Desired Outcome' to show what I want as the outcome.

Conditions...
- For every change in 'Student#' if any record 'Location' includes a "CYB" then column H should display 'Hybrid' for that specific record

- For every change in 'Student#' if any record 'Location' does not include a "CYB" then column H should display 'InPerson' for that specific record

- For every change in 'Student#' if any record 'Location' includes only "CYB" then column H should display 'OnLine' for that specific record Test-1.xlsx
LVL 14
###### Who is Participating?

Commented:
With that clarification I think the results would be different. Try this revised formula in H2

=IF(COUNTIFS(A:A,A2,D:D,D2,G:G,"CYB")=COUNTIFS(A:A,A2,D:D,D2),"Online",IF(COUNTIFS(A:A,A2,D:D,D2,G:G,"CYB"),"Hybrid","In Person"))

see attached

regards, barry
26922831v2.xlsx
0

Commented:
I've used this formula:

=IF(G2="CYB","OnLine",IF(SEARCH("CYB",G2)=1,"Hybrid","InPerson"))

But I don't get the same results like you have on column I. Please take a look at the attached file.

Let's see if I understood right:

1) if value on cell G2 is "CYB" then the returning result should be OnLine - you mentioned Hybrid on cell I2?!

if any record 'Location' includes only "CYB" then column H should display 'OnLine'

2) if value on cell G2 include "CYB" then the returning result should be Hybrid - in this case there should not be any Hybrid result on column I!

3) if value on cell G2 does not include "CYB" then the returning result should be InPerson

Are this the rules that you want?

jppinto
Test-1-1-.xlsx
0

Commented:
I think you can use this formula in H2 copied down to get those results

=IF(COUNTIFS(A:A,A2,G:G,"CYB")=COUNTIF(A:A,A2),"Online",IF(COUNTIFS(A:A,A2,G:G,"CYB"),"Hybrid","In Person"))

see attached

regards, barry
26922831.xlsx
0

Author Commented:
Ok let me clarify...and I also missed another criteria in my discription...sorry :)

There are three records with the 'Student#' = 100, for the same 'ReportTerm' = 05/FR
Each of those records has a different Location.
- If any one of those Locations equals CYB, then display "Hybrid" (for all three records with the same  'ReportTerm' for that 'Student#')
- If any one of those Locations does nor equal CYB, then display "InPerson" (for all three records with the same  'ReportTerm' for that 'Student#')
- If all of those Locations equal CYB, then display "OnLine" (for all three records with the same  'ReportTerm' for that 'Student#')

Hope this helps clarify
0

Commented:
A sample file might be easier
0

Author Commented:
Thank you! barryhoudini
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.