Count rows based on data in any of 3 columns, then average numbers

[toshimarise]

Thank you in advance for your assistance.

I need to perform two related functions on a set of data. Here is a sample of the spreadsheet, which is actually around 500 lines long and will be growing in the future:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Case 1[/TD]
[TD]Case 2[/TD]
[TD]Case 3[/TD]
[/TR]
[TR]
[TD]Aa23[/TD]
[TD]none (24)[/TD]
[TD]none (31)[/TD]
[TD]none (14)[/TD]
[/TR]
[TR]
[TD]Bb34[/TD]
[TD]7[/TD]
[TD]none (38)[/TD]
[TD]none (45)[/TD]
[/TR]
[TR]
[TD]Jj45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Cc56[/TD]
[TD]4[/TD]
[TD]none (14)[/TD]
[TD]none (18)[/TD]
[/TR]
[TR]
[TD]GG67[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]none (15)[/TD]
[/TR]
[TR]
[TD]Zz78[/TD]
[TD]none (14)[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

First, I need to count how many people (unique IDs) have a number greater than 0 in at least one of the columns, ignoring anything marked "none". In this case it should return "4".

Second, I need an average of the non-0 numbers, again ignoring anything marked "none". In this case it should return "4.2".

I am having a ridiculously hard time with this. I have tried a variety of sumproduct and countif solutions, and even one promising sumproduct(sign((etc)). But I just can't get my head around excluding text, excluding 0s, and only counting each row once, all at the same time.

Thanks!

 

[Michael M]

Heres one way, but shouldn't the average be 5.25 ???........7,4,3,3,4 divided by 5

Excel 2003
	A	B	C	D	E	F
1	ID	Case 1	Case 2	Case 3
2	Aa23	none (24)	none (31)	none (14)	0	0
3	Bb34	7	none (38)	none (45)	1	7
4	Jj45	0	0	0	0	0
5	Cc56	4	none (14)	none (18)	1	4
6	GG67	3	3	none (15)	2	6
7	Zz78	none (14)	4	0	1	4
8					4	5.25
Sheet1

Cell Formulas
Range	Formula
E2	=COUNTIF(B2:D2,">0")
E3	=COUNTIF(B3:D3,">0")
E4	=COUNTIF(B4:D4,">0")
E5	=COUNTIF(B5:D5,">0")
E6	=COUNTIF(B6:D6,">0")
E7	=COUNTIF(B7:D7,">0")
E8	=COUNTIF(E2:E7,">0")
F2	=SUMIF(B2:D2,">0",B2:D2)
F3	=SUMIF(B3:D3,">0",B3:D3)
F4	=SUMIF(B4:D4,">0",B4:D4)
F5	=SUMIF(B5:D5,">0",B5:D5)
F6	=SUMIF(B6:D6,">0",B6:D6)
F7	=SUMIF(B7:D7,">0",B7:D7)
F8	=SUM(F2:F7)/COUNTIF(F2:F7,">0")

 

[markmzz]

Maybe this:

First-> =SUMPRODUCT(--(MMULT(--ISNUMBER(B2:D7),ROW($1:$3))*((B2:B7>0)+(C2:C7>0)+(D2:D7>0))>0))

Second-> =AVERAGEIF(B2:D7,">0",B2:D7)

Markmzz

 

[markmzz]

A small modification:

First-> =SUMPRODUCT(--(MMULT(ISNUMBER(B2:D7)*(B2:D7>0),ROW(1:3))>0))

Markmzz

 

[toshimarise]

MichaelM, thanks for your answer, but I can't add helper columns to the data. (Also your average is different because you're averaging the lines, not the individual cases.)

First-> =SUMPRODUCT(--(MMULT(ISNUMBER(B2:D7)*(B2:D7>0),ROW(1:3))>0))

Second-> =AVERAGEIF(B2:D7,">0",B2:D7)

Markmzz

Thanks! These work great when placed on the same sheet as the data. But the statistics actually go on a separate sheet in the workbook, and when I put the second formula (the sumproduct(etc)) in another sheet I get a #VALUE error citing a circular reference. (I cut & pasted, so it added the sheet name to the ranges automatically.)

This is the code that I put on Sheet2, which returns a #VALUE:

=SUMPRODUCT(--(MMULT(ISNUMBER(Sheet1!B2:D7)*(Sheet1!B2:D7>0),ROW($B2:$B2))>0))

You guys are great!

 

[toshimarise]

I figured out the problem but I can't figure out how to edit my previous post. The row #s in the sumproduct(etc) change to those bizarre $-marked cells instead of 1:3. Not sure why that happens, but luckily it's an easy fix.

You would not believe how much time I spent poking at this before giving up and asking for help. I say again: you guys are great!

 

[markmzz]

I figured out the problem but I can't figure out how to edit my previous post. The row #s in the sumproduct(etc) change to those bizarre $-marked cells instead of 1:3. Not sure why that happens, but luckily it's an easy fix.

You would not believe how much time I spent poking at this before giving up and asking for help. I say again: you guys are great!

Hi Toshimarise.

The part of the formula ROW(1:3) is necessary in the example of the post #1 because I need three numbers (three columns of cases - B, C and D) to multiply the array ISNUMBER(Sheet1!B2:D7)*(Sheet1!B2:D7>0) and turn it into an numeric array with six rows and one column. I didn't use $ (for absolute reference - fix position in the sheet) because there was no need to copy the formula in that case (and anyway I need three numbers >0).

Markmzz