Does COUNTIF work if adding non-sequential cells i.e. not a range

I'm trying to count the number of times a letter appears in a row, but the range is not sequential. So I have a number of columns in sections, Column A-E is one section, F-J is next section, K-O the next etc., but I only want to know if the last column in each of these sections contain a specific text - the word Excellent, so I can't use COUNTIF(A1:O1, "Excellent") as there are instances in B and C, for example that would also show the same word. I've tried COUNTIF (E1, "Excellent") (J1, "Excellent") (O1, "Excellent) and so on, but I cannot get this to work. Am I trying the impossible - will it only work within a range?

Thank you if you can help.

If columns E,J,O, etc have a common heading that differers from A:D,F:I,K:N, etc then you could use that with COUNTIFS.

Otherwise you could try sumproduct

=SUMPRODUCT(--(A1:O1="Excellent"),--(MOD(COLUMN(A1:O1),5)=0))

Originally Posted by TheExcelphile

Try this:

COUNTIF(E1,"Excellent")+COUNTIF(J1,"Excellent")+COUNTIF(O1,"Excellent")

Don't forget to click the little star to the left of this post if you feel I helped!

Thank you - can't find the little star, but it was a great help.

Thank you - I'd like to use the bottom option as I have about 25 cells to include, so it would be quicker than selecting each one and I think I understand the concept, but I can't get it to work. I simplified my question for the thread, but basically my sections range from S1 to HT1 and are split in 7 columns each. Each section is for a different subject, so all the headings are different unfortunately, although the last column - the 7th, contains the text ATL in each one. So I need to know if Y1, AF1, AM1, AT1, BA1 - all the way through to HT1, contain Excellent (which I've put as E), Good (which is shown as G) or Unsatisfactory (which is shown as U). I've tried changing your 5 to 7 (assuming that the 5 was for every 5th column) and put S1:HT1 instead of A1:01 for the range and MODCOLUMN, but it doesn't add anything up.
Sorry to ask again, but if you can help further, it would be great. Thank you.

Obvious once you know it's there. Thank you.

Originally Posted by jason.b75

If columns E,J,O, etc have a common heading that differers from A:D,F:I,K:N, etc then you could use that with COUNTIFS.

Otherwise you could try sumproduct

=SUMPRODUCT(--(A1:O1="Excellent"),--(MOD(COLUMN(A1:O1),5)=0))

Sorry - I'm very new to this. Message for Jason.b75:- Thank you - I'd like to use the bottom option as I have about 25 cells to include, so it would be quicker than selecting each one and I think I understand the concept, but I can't get it to work. I simplified my question for the thread, but basically my sections range from S1 to HT1 and are split in 7 columns each. Each section is for a different subject, so all the headings are different unfortunately, although the last column - the 7th, contains the text ATL in each one. So I need to know if Y1, AF1, AM1, AT1, BA1 - all the way through to HT1, contain Excellent (which I've put as E), Good (which is shown as G) or Unsatisfactory (which is shown as U). I've tried changing your 5 to 7 (assuming that the 5 was for every 5th column) and put S1:HT1 instead of A1:01 for the range and MODCOLUMN, but it doesn't add anything up.
Sorry to ask again, but if you can help further, it would be great. Thank you.

You are almost correct changing 5 to 7, If your data still started in column A,H,O, etc then it would work as the last column will always be a multiple of 7. Because your range is offset from this, the formula needs to be able to allow for the difference, the easiest way would be to change =0 to =4

Column Y is the 25th column, 25 / 7 = 3 with a remainder of 4 (the MOD value).

There might however be a simpler way to achieve the same, working with the first range, S1:Y1, Y1 contains "Excellent" (or value to count). Which cell contains the text "ATL", and would this be the same relative to all the other groups of 7 cells?

Assuming T1 contains "ATL" then

=COUNTIFS(T1:HO1,"ATL",Y1:HT1,"Excellent")

Should also work.

Originally Posted by jason.b75

You are almost correct changing 5 to 7, If your data still started in column A,H,O, etc then it would work as the last column will always be a multiple of 7. Because your range is offset from this, the formula needs to be able to allow for the difference, the easiest way would be to change =0 to =4

Column Y is the 25th column, 25 / 7 = 3 with a remainder of 4 (the MOD value).

There might however be a simpler way to achieve the same, working with the first range, S1:Y1, Y1 contains "Excellent" (or value to count). Which cell contains the text "ATL", and would this be the same relative to all the other groups of 7 cells?

Assuming T1 contains "ATL" then

=COUNTIFS(T1:HO1,"ATL",Y1:HT1,"Excellent")

Should also work.

Huge thanks - this has saved me so much time. Worked beautifully. Lesson learned - be more accurate with my questions .