Count if row range contains a value greater than 0

**Groovicles** · 01-26-2016, 01:53 PM

Hi everyone,

I have a worksheet with 50 rows. Column A has names. Columns B-M with either be blank or have 1.

What I'm trying to do is count the number of rows (within the 50 rows of names) that have at least 1 cell in their respective ranges (B-N) that are 1.

Any ideas?

Thanks!!

**Pete_UK** · 01-26-2016, 02:14 PM

Assuming you have a header row and that your data starts in row 2, you can put this formula in cell O2:

=COUNT(B2:N2)

and then copy that down to the bottom of your data. Then you can use this formula:

=COUNTIF(O:O,">0")

to give you the count you require.

Hope this helps.

Pete

**Ron Coderre** · 01-26-2016, 02:16 PM

If you want a single formula solution...

With your data in cells A1:M51, Row_1 has headings
B2:M51 contains blanks or 1's
This formula returns the count of rows where B:N has at least one value

Please Login or Register  to view this content.

Is that something you can work with?

**Groovicles** · 01-26-2016, 02:17 PM

Hi Pete,

Thanks very much. I have to apologies, I left my desk and realized I should have clarified. I'm trying to build a single formula for exactly what you write (and I did originally anyways). Something that combines the count across each row and then countif down the column.

Any ideas?

Thanks!

**Groovicles** · 01-26-2016, 02:23 PM

Hi Ron,

Thanks. Yes, I'm absolutely looking for a single formula. To clarify, the names in column A start in row 3, and the data (either 1 or blank) is B3:M48. I used your formula as is and got 0. Any ideas?

Thanks!

**Ron Coderre** · 01-26-2016, 02:30 PM

Then this would be the formula

Please Login or Register  to view this content.

However, your previous results indicate that the values are not numeric.

Is that something you can work with?

**Groovicles** · 01-26-2016, 02:46 PM

Hi Ron,

That values in B - M are numeric. The values in column A are names. Thank you for the formula. The results seems to be a count of the # of names in column A, from rows 3 - 48 = 46. It does not seem it counted the associated rows that had 1's in them.

**Ron Coderre** · 01-26-2016, 03:07 PM

Here's my set up:

	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Name	Phase_01	Phase_02	Phase_03	Phase_04	Phase_05	Phase_06	Phase_07	Phase_08	Phase_09	Phase_10	Phase_11	Phase_12
3	Alpha	1					1		1		1
4	Bravo		1	1	1		1	1		1	1	1	1
5	Charlie
6	Delta				1		1	1		1			1
7	Echo	1	1	1	1	1		1	1	1	1		1
8	Foxtrot	1	1				1						1
9	Golf
10	Hotel
11	India
12	Juliet
13	Kilo
14	Lima				1		1			1			1
15	Mike	1	1		1	1	1	1	1	1
16	November		1	1	1			1	1	1	1
17	October	1	1		1	1
18	Papa		1			1	1	1	1	1
19	Quebec	1		1	1					1		1	1
20	Romeo
21	Sierra
22	Tango
23	Uniform
24	Victor
25	Whiskey
26	X-Ray
27	Yankee
28	Zulu
29	Alpha
30	Bravo
31	Charlie	1		1				1			1	1	1
32	Delta		1			1						1	1
33	Echo		1	1		1	1		1		1	1
34	Foxtrot		1				1		1				1
35	Golf					1	1			1		1
36	Hotel		1	1			1	1		1
37	India	1		1	1	1	1	1	1		1
38	Juliet		1		1		1		1	1		1	1
39	Kilo		1		1	1	1		1			1	1
40	Lima		1			1	1		1		1
41	Mike	1			1	1			1	1	1	1	1
42	November	1	1	1	1		1			1		1	1
43	October		1	1		1	1			1	1	1
44	Papa	1	1			1			1				1
45	Quebec	1	1			1	1				1	1
46	Romeo	1			1		1		1			1	1
47	Sierra				1	1			1	1		1	1
48	Tango	1	1					1	1	1	1	1

This is the formula for numeric values:

Please Login or Register  to view this content.

The result is: 29

**Groovicles** · 01-26-2016, 03:10 PM

Thank you sir, that seemed to work perfectly.

**RedSummer** · 03-17-2016, 02:36 PM

Ron this is a really useful formula, would you be able to explain to me its moving parts/how it works?
Thank you and rep added
Red

**Ron Coderre** · 03-18-2016, 06:55 AM

in this formula:
O2: =COUNT(INDEX(1/SUBTOTAL(9,OFFSET(B2:M2,ROW(A3:A48)-2,)),0))

this section: OFFSET(B2:M2,ROW(A3:A48)-2,)
references B2:M2 and sequentially increments to reference each row in the range.

The SUBTOTAL function happens to handle the incrementing nicely and returns the sum of each referenced range. In this case it will return the sum for the referenced row or zero, if there are no values.

By dividing each sum into 1, the results are a fraction or #DIV/0!

Since the results are contained in an array, we can either commit the formula with CTRL+SHIFT+ENTER,
....OR...implement a nice feature of the INDEX function:
In most C+S+E situations, if you wrap the array section in and INDEX function, Excel properly interprets the results as an array.

The COUNT function returns the count of numeric results...the number of rows with at least one numeric entry.

I hope that helps.

Count if row range contains a value greater than 0

LinkBack

Thread Tools

Rate This Thread

Display

Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Re: Count if row range contains a value greater than 0

Thread Information

Users Browsing this Thread

Similar Threads

Count number of dates in a range by using greater or less than

[SOLVED] again count for range greater than another

[SOLVED] get count for range greater than another

count the number of cells in a range that are greater than 36 & less than 48

Count instances where one cell is greater than another WITHIN a date range

Array to count how many days within a range are greater than 5

Count range greater than and less than..

[SOLVED] How do I count numbers in a range that are greater than and less .

Bookmarks

Bookmarks

Posting Permissions