Need help with a Multiple COUNTIF and IF statement

Hi need some excel help, does anyone know of a formula in Excel to count cells in a range if they do not equal a ?U? and then double the count in a cell one of the columns cells in that range.

For example cells row one column A:C have 3 grades All are 9-1 grades so would need that to equal 4 in Cell D
However if row 2 contains a U in A:B but a 9-1 in column C it would enter 2 as the count in Column D.
I?ve got the count only formula if not a u but can?t quite work out how to add a formula to double the count in in column C if it?s not a U
=if(countif(RANGE,?<>U?)=0,0, (countif(RANGE,?<>U?)+1))

The above still counts a U so i get 3 in cell D4 instead of 2

Hope that makes sense? I've attached an example to help understand

Try this in D2:

=IF(COUNTIF(C2,"*u*"),0,2)+COUNTIF(A2:B2,"<>*u*")

then copy down.

Hope this helps.

Pete

Thanks Pete_Uk that worked great. However how could i modify this if either Cell A or B Contained a U, then count them as 2 still so in other words if any time A2,B2 or C2 has a U grade I would like it to count 2 instead of 1 to give a total of 3 still in D2? Does that make sense?

Actually i think i may have solved it by removing the u in the final countif to below:

=IF(COUNTIF(C2,"*u*"),0,2)+COUNTIF(A2:B2,"<>*&*")

Would there be any problem with copying down that to the rows?

Hi Pete_Uk,or anyone else that may be able to offer some help.

I have now come to realise that I need the formula to work, slightly different in that it still needs to count any grade between 9-1 in C2 as double grade, however if there is a U in either A2 or B2 they are counted as double but not if they are a U grade.

I have attached an example of what i would like it to achieve in column E with highlighted cells (Yellow for error and Green for Correct values)in the attached file if someone could assist with this, i would greatly appreciate it.

You can use this formula in D2:

=COUNTIF(A2:C2,"<>u")*2

then copy down as required.

Hope this helps.

Pete

Perhaps a more robust formula to use would be:

=COUNTIFS(A2:C2,"?*",A2:C2,"<>u")*2

The first part means "not empty" and the second part "not equal to u", just in case some of the cells have not been filled in.

Hope this helps.

Pete

Hi Pete thanks for your help really appreaciate, that works great apart from when all 3 grades are full i still need it to count the total to 4. Currently using the formula you gave it counts 6. (so where no U's exist it would like it to count the total as 4. If that makes sense?)

Just wrap a MIN around it, i.e.:

=MIN(4,COUNTIFS(A2:C2,"?*",A2:C2,"<>u")*2)

Hope this helps.

Pete

Thanks for that Pete, much appreciated however it's still counting A2 and B2 as 4 instead of 2 if they both contain a 9-1 grade. For example if I have 2 grades from 9-1 in A2 and B2 and a U in C2, I'd like the count to be 2 and not 4. However if there is a 9-1 grade in either A2 and B2 and a 9-1 grade in C2 then this would count as 4. Sorry, it's actually quite confusing and hopefully that makes sense and I really do appreciate the help. I've attached another file with annotations and hope it's more understandable?

Originally Posted by holykimura

... it's actually quite confusing ...

It sure is, but this one gives you the results that you are expecting in your latest file:

=MIN(IF(COUNTIFS(A2:C2,"?*",A2:C2,"<>u")=2,2,4),COUNTIFS(A2:C2,"?*",A2:C2,"<>u")*2)

Let's hope this is the end of it, now.

Pete

Thanks Pete that has worked perfectly! Your help is greatly appreciated!

Really sorry about this Pete, but I made a mistake in the "what I expected" column yesterday in that I didn't included the scenario where if Either A2 and B2 had had a grade other than a U (9-1) AND C2 had a grade (9-1) and not a U, then that should count as 4 and not 2. I have uploaded an image with cells in red where they are incorrect and the example file again. Example 4.JPG

You seem to be making this up as we go along. I suggest you put together a set of examples that show ALL possible variations and combinations, along with your expected outcomes for those, and then I'll take a stab at solving them all in one go. You haven't explained why you are doubling-up in some instances and not in others.

Pete

Originally Posted by Pete_UK

You seem to be making this up as we go along. I suggest you put together a set of examples that show ALL possible variations and combinations, along with your expected outcomes for those, and then I'll take a stab at solving them all in one go. You haven't explained why you are doubling-up in some instances and not in others.

Pete

I'm honestly not making this up as I'm going a long and I appreciate that it may feel like that. I've got really bad vertigo at the moment so my concentration and focus is all over the place. The Variations and combinations are complete now in the last example. The reason for doubling up is Maths counts as a double grade regardless of what is in either of the English columns provided it's not a U. However because there are two possible English grades, if either one is a U, the other English grade is double weighted if that makes sense?

Your explanation doesn't explain why a single English grade counts as 2, whereas 2 pass grades also count as 2, but this formula gives the results that you are expecting:

=IF(C2="u",0,2)+IF(COUNTIF(A2:B2,"u")=2,0,2)

Do you agree?

Pete

That appears to be working now, yes it's a little confusing. English is counted as 2 grades because if either English Literature or English Language is a U grade, then the 9-1 grade from the one that doesn't have a U is doubled to count as 2 grades. Thanks again for your help and patience, you are a star!

Well, we finally got there !

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Originally Posted by Pete_UK

Well, we finally got there !

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

We did indeed finally get there!! I've marked it as solved and I added to your reputation using the star on the post. Thanks again for your help!!