SUMIF and COUNTIF help

[towners]

Hello, newbie here again...

This time I want to calculate the average perofrmance % of 8 lines, the data isn't in one set of rows and some lines may not have values so I'm trying to account for this in my summary.

The code I'm struggling with is this...

=SUMIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")/COUNTIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")

Many thanks for any help...

 

[mikerickson]

What are the formulas in C5, C10, etc.?
To get the percentage of the aggregate data accuratly, one has to work with the base data rather than averaging sub-percentages.

 

[towners]

In those cells it is a % of available time for production, I also need to average productivity and quality in the same way.

The formula in the cells is

=IF(ISBLANK(VLOOKUP($B$1&"Line "&$A$20&$B20,'Daily Data'!$A:$AA,19,FALSE)),"",VLOOKUP($B$1&"Line "&$A$20&$B20,'Daily Data'!$A:$AA,19,FALSE))

 

[DanD]

Hello, newbie here again...

This time I want to calculate the average perofrmance % of 8 lines, the data isn't in one set of rows and some lines may not have values so I'm trying to account for this in my summary.

The code I'm struggling with is this...

=SUMIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")/COUNTIF(C5,C10,C15,C20,C25,C30,C35,C40,">0")

Many thanks for any help...

Purely addapting your formula.. Try...

=SUMIF(C5,C10,C15,C20,C25,C30,C35,C40,">"&0)/COUNTIF(C5,C10,C15,C20,C25,C30,C35,C40,">"&0)

 

[towners]

I fixed it and thought I'd share what I did witth others...

=(SUM(SUMIF(C5,">0"),SUMIF(C10,">0"),SUMIF(C15,">0"),SUMIF(C20,">0"),SUMIF(C25,">0"),SUMIF(C30,">0"),SUMIF(C35,">0"),SUMIF(C40,">0"))/SUM(COUNTIF(C5,">0"),COUNTIF(C10,">0"),COUNTIF(C15,">0"),COUNTIF(C20,">0"),COUNTIF(C25,">0"),COUNTIF(C30,">0"),COUNTIF(C35,">0"),COUNTIF(C40,">0")))

Paul

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(MOD(ROW(C4:C40)-ROW(C4),5)=0,IF(C4:C40>0,C4:C40)))

Hope this helps!

 

[nbrcrunch]

similar to Dominic's above. Also an array formula.


=AVERAGE(IF(MOD(ROW(C5:C40),5)=0,IF(C5:C40>0,C5:C40)))
.

 

[nbrcrunch]

I ran a test of both Dominic's and my formulas using the following

In column C filled from cell C1 to C40 with the numbers 1 thru 40.

Using my formula, the average reported 22.5. Dominic's 21.5. Through manual computaiton, the correct is 22.5

Then I put zero in C20. Mine reported 22.857. Dominic's remained unchanged at 21.5

 

[Domenic]

Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(MOD(ROW(C4:C40)-ROW(C4),5)=0,IF(C4:C40>0,C4:C40)))

Hope this helps!

Picked up the wrong starting reference. The formula should have been...

=AVERAGE(IF(MOD(ROW(C5:C40)-ROW(C5),5)=0,IF(C5:C40>0,C5:C40)))

By the way, this part of the formula...

-ROW(C5)

...is added for robustness.

Hope this helps!

 

[nbrcrunch]

Dominic: I readily bow to your obvious expertise. Please explain the -ROW(C5). I couldn't see how that helped but you know (I hope) how I love learning.