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
BigBadWolf_000Asked:
Who is Participating?
 
barry houdiniCommented:
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
 
jppintoCommented:
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.
Your rules are not clear!

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
 
barry houdiniCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
BigBadWolf_000Author 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
 
EmenizerCommented:
A sample file might be easier
0
 
BigBadWolf_000Author 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.

All Courses

From novice to tech pro — start learning today.