Count values greater than zero in a row that has a defined value in a column

[nady94]

Hi,

I'm trying to count how many weeks a specific Product has been active. The Product has been active if they have a value for that week.

The value in Column B has to match a defined value (i.e. A, B, C, D - the data in cells B12-B15), and then the data from columns C to G have to be greater than zero/not blank. The desired results are in cells C12-C15. Is this possible?

Let me know if there's any additional information I can give that would be helpful.

Thanks!

Book1
	A	B	C	D	E	F	G	H
1
2		Product	Week 1	Week 2	Week 3	Week 4	Week 5
3		A			2	3	4
4		B	2	3	1
5		C	1	2	1	4	2
6		D	1	1
7
8
9		Desired Result
10
11		Product	Weeks Active
12		A	3
13		B	3
14		C	5
15		D	2
16
Sheet1

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUMPRODUCT(($B$3:$B$6=B12)*($C$3:$G$6>0))

 

[Joe4]

This can be done with a simple COUNTIF. Place this formula in C12 and copy down for C15:

=COUNTIF(C3:G3,"<>")

 

[nady94]

The SUMPRODUCT works perfectly so thank you!

I would've used COUNTIF but the actual values in column B will change as Products are added in different orders and names change. I always prefer to use formulas that have a bit more protection against those sorts of things if possible.

Thanks both!

 

[Fluff]

Glad we could help & thanks for the feedback.