# SUMIF and COUNTIF help

#### towners

##### Board Regular
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...

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### mikerickson

##### MrExcel MVP
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

##### Board Regular
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))

Last edited:

#### DanD

##### Board Regular
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...

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

#### towners

##### Board Regular
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

##### MrExcel MVP
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

##### Well-known Member
similar to Dominic's above. Also an array formula.

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

Last edited:

#### nbrcrunch

##### Well-known Member
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

##### MrExcel MVP
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)

Hope this helps!

#### nbrcrunch

##### Well-known Member
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.

Replies
27
Views
567
Replies
1
Views
182
Replies
2
Views
366
Replies
3
Views
112
Replies
8
Views
281

1,191,166
Messages
5,985,050
Members
439,935
Latest member
Monty238

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back