Results 1 to 4 of 4

IF Question

This is a discussion on IF Question within the Excel Questions forums, part of the Question Forums category; Hi, I'm trying to count GCSE results. I want to count if a pupil has 5 GCSEs (A* - C ...

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Posts
    210

    Smile IF Question

    Hi,

    I'm trying to count GCSE results. I want to count if a pupil has 5 GCSEs (A* - C grades) including Maths & English. Maths is P2 and English is V2.

    I'm trying the below formula but it won't work. Can you help?

    =IF(AND(OR(P2={"A*","A","B","C"}),OR(V2={"A*","A","B","C"})),IF(SUM(COUNTIF(P2,V2,AB2,AI2,AN2,AT2,AZ2,BF2,BL2,BR2,BX2,CD2,CJ2,CP2,CV2,DB2,DH2,DN2,DT2,DZ2,EF2,EL2,{"A*","A","B","C"}))>=5,"Yes","No"),"No")

    Many thanks,
    Russ

  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    4,910

    Default IF Question

    Can you post an example of your data - there may be an alternative way to count what you're after

  3. #3
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default Re: IF Question

    Howdy Russ!

    OK I have only modified your current formula a bit and I am not making any claims this is the best way to do it but this works:

    =IF(AND(OR(P2={"A*","A","B","C"}),OR(V2={"A*","A","B","C"})),IF(SUM(COUNTIF(INDIRECT({"P2","V2","AB2","AI2","AN2","AT2","AZ2","BF2","BL2","BR2","BX2","CD2","CJ2","CP2","CV2","DB2","DH2","DN2","DT2","DZ2","EF2","EL2"}),{"A*";"B";"C"}))>=5,"Yes","No"),"No")

    Or this if you may have invalid results beginning with an A (eg 'Absent'):

    =IF(AND(OR(P2={"A*","A","B","C"}),OR(V2={"A*","A","B","C"})),IF(SUM(COUNTIF(INDIRECT({"P2","V2","AB2","AI2","AN2","AT2","AZ2","BF2","BL2","BR2","BX2","CD2","CJ2","CP2","CV2","DB2","DH2","DN2","DT2","DZ2","EF2","EL2"}),{"A~*";"A";"B";"C"}))>=5,"Yes","No"),"No")
    Last edited by Richard Schollar; Mar 30th, 2010 at 03:59 AM.
    Richard Schollar

    Using xl2013

  4. #4
    Board Regular
    Join Date
    Jun 2007
    Posts
    210

    Default Re: IF Question

    Thanks!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com