# Averageifs two criterias

#### ApolloID

##### Well-known Member
Hi, I need a formula that will give the average based on two criterias.

In sheet 1 i have:

 Supplier Document Percentage supplier 1 doc1 50% supplier 1 doc1 45% supplier 1 doc1 90% supplier 1 doc2 99% supplier 1 doc2 88%

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

In Sheet 2 i need the average for supplier, based on average of documents.

The average for doc1 is 62%, and for doc2 is 94%.

So, the result should be:

 supplier 1 78%

<tbody>
</tbody>

Can this be done?

Thanks,
Appolloid

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Is this what you're after?

=AVERAGE(AVERAGEIFS(\$C\$2:\$C\$6,\$A\$2:\$A\$6,"Supplier 1",\$B\$2:\$B\$6,"doc1"),AVERAGEIFS(\$C\$2:\$C\$6,\$A\$2:\$A\$6,"Supplier 1",\$B\$2:\$B\$6,"doc2"))

Hi Zakkaroo,

The result is what i need, but my document has around 30.000 rows, and i need a formula that does not contain "Supplier1" or "doc1...doc2"....etc.
With supplier it's easy, because i can replace "Supplier1" with A1 in which i could put Supplier1. however, i cannot have in formula the document1, because i have so many documents.

The best would be a formula that will look at supplier1, and make the average per each document, like your formula, but without specify the documents names.

Can this be done?
Thanks.

Sheet1, A1:C6, houses the data, the headers included.

Sheet2, A2, houses a criterion value like supplier 1.

In B2 of Sheet2 control+shift+enter, not just enter...

=AVERAGE(IF(ISNUMBER(AVERAGEIFS(Sheet1!\$C\$2:\$C\$6,Sheet1!\$A\$2:\$A\$6,A2,Sheet1!\$B\$2:\$B\$6,IF(FREQUENCY(IF(Sheet1!\$A\$2:\$A\$6=A2,MATCH(Sheet1!\$B\$2:\$B\$6,Sheet1!\$B\$2:\$B\$6,0)),ROW(Sheet1!\$A\$2:\$A\$6)-ROW(Sheet1!\$A\$2)+1),Sheet1!\$B\$2:\$B\$6))),AVERAGEIFS(Sheet1!\$C\$2:\$C\$6,Sheet1!\$A\$2:\$A\$6,A2,Sheet1!\$B\$2:\$B\$6,IF(FREQUENCY(IF(Sheet1!\$A\$2:\$A\$6=A2,MATCH(Sheet1!\$B\$2:\$B\$6,Sheet1!\$B\$2:\$B\$6,0)),ROW(Sheet1!\$A\$2:\$A\$6)-ROW(Sheet1!\$A\$2)+1),Sheet1!\$B\$2:\$B\$6))))

If you would want to install the V() function, we can have:

=AVERAGE(IF(ISNUMBER(V(AVERAGEIFS(Sheet1!\$C\$2:\$C\$6,Sheet1!\$A\$2:\$A\$6,A2,Sheet1!\$B\$2:\$B\$6,IF(FREQUENCY(IF(Sheet1!\$A\$2:\$A\$6=A2,MATCH(Sheet1!\$B\$2:\$B\$6,Sheet1!\$B\$2:\$B\$6,0)),ROW(Sheet1!\$A\$2:\$A\$6)-ROW(Sheet1!\$A\$2)+1),Sheet1!\$B\$2:\$B\$6)))),V()))

VBA of V() is...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

The first formula works great. It's a little slow, but is working.

Can you please tell me what do you mean with install V() function?

Thanks allot.

Apolloid

The first formula works great. It's a little slow, but is working.

The second formula will be faster because it avoids computing twice.

Can you please tell me what do you mean with install V() function?

Execute Alt+F11.
Choose This Workbook.
Run Insert | Module.
Copy the code of V() and paste it in the empty pane.
Run File | Close and Return...

The second formula can now be run.

Thanks allot.

Apolloid

You are welcome.

I receive an error when i put the formula:
"the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

And if i hit ok, the "MATCH" from the formula is highlighted.

Can you help me with this issue?

Thanks,
Apolloid.

I receive an error when i put the formula:
"the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format."

And if i hit ok, the "MATCH" from the formula is highlighted.

Can you help me with this issue?

Thanks,
Apolloid.

See the workbook that implements both versions, with V() and without: https://dl.dropboxusercontent.com/u/65698317/ApolloID averageifs.xlsm.

Thank you for the file! It works great.

Thank you.
Apolloid

Thank you for the file! It works great.

Thank you.
Apolloid

You are welcome.

Replies
3
Views
454
Replies
6
Views
1K
Replies
3
Views
505
Replies
8
Views
402
Replies
6
Views
428

1,207,169
Messages
6,076,910
Members
446,239
Latest member
Home Nest

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