Count cells in a table only for specific columns

[7corona7]

Hey!

I am stuck on a simple yet seemingly difficult task for excel. All I want to do is count the number of cells for specific columns, yet I haven't found a formula which could work.

[TABLE="width: 500"]
<tbody>[TR]
[TD]j
[/TD]
[TD]n
[/TD]
[TD]n
[/TD]
[TD]j
[/TD]
[TD]j
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]99
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]99
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]99
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]


I need to count the columns for "j" and "n" separately (and add them together), but only for the numbers <2. So for "j" I should receive a result of 5+5+6=16 and for "n" 5+6=11.
Does anybody have an insight into this problem?

Thanks

 

[Greenwald]

=COUNTIF(A2:A7,"<2") and replace with each specific range.

Is that good enough or do you need to automate the addition of columns as well?

 

[7corona7]

=COUNTIF(A2:A7,"<2") and replace with each specific range.

Is that good enough or do you need to automate the addition of columns as well?

thanks, so there is no way around of doing this manually for each column? (I have a large datasheet)

and yes I would need the total sum of all of the "j" and "n" counts. Which means I guess I could just use the SUM formula to add all the values.

 

[XOR LX]

Hi.

Assuming the table as you give it is in A1:E7 (with headers in row 1):

=SUMPRODUCT((A1:E1="j")*(A2:E7<2))

Change the "j" to "n" as desired.

Note that, since your example did not include any blank cells, I have assumed that this will always be the case and so have not accounted for this possibility in the above formula.

Regards

 

[7corona7]

Hi.

Assuming the table as you give it is in A1:E7 (with headers in row 1):

=SUMPRODUCT((A1:E1="j")*(A2:E7<2))

Change the "j" to "n" as desired.

Note that, since your example did not include any blank cells, I have assumed that this will always be the case and so have not accounted for this possibility in the above formula.

Regards

this is an awfully late responde, but thanks this is exactly what I wanted! =)