How do I find the total number of the same number/letter in a row

[Anne-Marie]

I am not trying to find the sum of numbers but want to evaluate how many
number 1s, 2s etc that I have without having to count manually. Also would
like to do the same with letters. Any suggestions would be much appreciated.

 

[Bob Phillips]

=COUNTIF(2:2,1)

=COUNTIF(2:2,"A")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

I am not trying to find the sum of numbers but want to evaluate how many
number 1s, 2s etc that I have without having to count manually. Also would
like to do the same with letters. Any suggestions would be much

appreciated.

 

[milnei]

countif does not sum all the instances, sumif does.

Just try it.

Cheers

Ian

 

[Anne-Marie]

Thanks Bob - that helped me with the first bit but am now trying to do
multiple criterias (see posting above), is it possible?

 

[Roger Govier]

Hi

The OP said she did not want sum. She wanted a count of the occurrences.
Bob's solution does just that.

Regards

Roger Govier

 

[Bob Phillips]

You mean where row 1 = 1 say, and row 2 = "A"?

=SUMPRODUCT(--(1:1=1),--(2:2="A"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Anne-Marie]

Maybe. I want to work out how many kids had grades 5c-7a for example. I have
all the information sent to me on excel - assessment grades are from 3c-7a (c
being lower than a) and effort grades are 1-4.
From a total of say 13 assessment grades, I want to work out how many are
above a certain boundary for each kid - really to see where problems may be
occurring, so I don't want a sum, just a total number above a certain value.

 

[Bob Phillips]

Tricky. Let's say you want to count all better than 3a, then best to count
all >= 3, an subtract and 3 and not = a, like

=COUNTIF(1:1,">=3*")-SUMPRODUCT(--(LEFT(1:1,1)="3"),--(RIGHT(1:1,1)<>"a"))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Anne-Marie]

I am totally confused by that Bob. As a total novice I am not sure what you
mean by 2:2 etc - Could you possibly explain? Cheers and thanks for your help
so far.

 

[Anne-Marie]

Me again! Assume this is a section of the spreadsheet. Now in each row, I
want to
2 4a 2 4a 1 3a 2 5c 1
1 4a 2 4a 1 4c 1 3a 2
3 4c 2 3b 2 3a 2 3a know how many grades are above 4a for each child but I
do not want to include numbers without letters because they are effort grades
not attainment grades. Is there any way to do that?