COUNTIF for multiple values in one cell

Hi all!

I have a single cell which always contains either a "0" or a score which can be "1 out of 1" or "2 out of 3" etc.

I am trying to get a COUNTIF to work which will count if the cell contains a "0", if it does not contain "0" then of the score in the cell it needs to count if the figures match so for example:

Cell contains a "0" - COUNTIF counts +1

If the cell contains "1 out of 1" it would still count +1 as the figures match, however, if the formula contained "2 out of 3" or "1 out of 2" etc. it would not count because it registers that the 2 and 3 or 1 and 2 do not match.

Hope this makes sense!

I am trying to do it with a COUNTIFS but struggling. This is what I have so far which essentially only counts the 0 and if the cell contains the exact text of 1 out of 1 rather than because the figures match and would not register if the cell contained another result, for example 2 out of 2 or 2 out of 3 etc.

=SUM(COUNTIFS(R6,{"0","1 out of 1"}))

Is this what you wanted, assuming data start in A1:

=IF(LOOKUP(10^10,--LEFT(A1,ROW($1:$10)))=ABS(LOOKUP(10^10,--RIGHT(A1,ROW($1:$10)))),1,0)

Assuming A1:A9 contains data

Try:

=SUMPRODUCT(--(RIGHT(A1:A9)-LEFT(A1:A9)=0))

Untitled.png

bebo, I do like that!!! But it may not be suitable, as it does not work for "12 out of 12". But maybe two digit numbers can not occur...

Originally Posted by Glenn Kennedy

bebo, I do like that!!! But it may not be suitable, as it does not work for "12 out of 12". But maybe two digit numbers can not occur...

Yes You may right.
In that case:

=SUMPRODUCT(--(RIGHT(SUBSTITUTE(A1:A9," ",REPT(" ",10)),10)-LEFT(SUBSTITUTE(A1:A9," ",REPT(" ",10)),10)=0))

Hi all,

Sorry I seem to be having trouble incorporating this into my formula, i have posted the full formula below:

HTML Code: 

This formula counts a number of conditions and then adds them up to a total of X out of 12.

The COUNTIF I am having a problem with, within this formula is for the R8 part. I need that part of the formula to COUNTIF R8 = "0" or if it contains any score which matches "1 out of 1","2 out of 2" etc. but will not count if the score does not match, "3 out of 4" for example.

Sorry, I am not good at excel or explaining very well, apologies!

Can you upload a sample file ?
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I have added an example work book.

Summary:

If the cell in R8 contains a 0 or matching score then it should count.
If the cell contains a score which does not match then it will not count.

Maybe in R28:

=(R5>=1)+IFERROR(R6>=1,1)+(R7<=120)+IF(R8=0,0,LEFT(R8,2)-RIGHT(R8,2)=0)+(R9<=28)+(R10<=90)+(R11<=28)+(R12<=21)+(R13<=40)+(R14>=0.25)+(R15<=0.35)+(R27>=1)&" out of 12"

Copy to R54 and so on.

Amazing! Thank you, that did the trick!

One additional request in regards to this! If i want to do conditional formatting to highlight the cell green if it equals 0 or the score matches and red if the score does not match, is that something possible?

For example, 1 out of 1, cell goes green, 2 out of 3, cell goes red?

Originally Posted by Catsonheat

One additional request in regards to this! If i want to do conditional formatting to highlight the cell green if it equals 0 or the score matches and red if the score does not match, is that something possible?

For example, 1 out of 1, cell goes green, 2 out of 3, cell goes red?

Format R8 as red.
Conditional formating formular of R8:
=OR(R8=0,LEFT(R8,2)-RIGHT(R8,2)=0)
Format as green.
Copy R8, paste format to R34,R60 and so on.