# Combine 'AverageIfs' and 'or'?

#### chinglnc

##### Board Regular
Hi

Is there anyway to combine 'averageifs' and 'or'

For example:

 Year Product Quality Country Score 2013 A Good UK 10 2013 A Good USA 10 2014 B Bad UK 20 2014 C Good UK 20

<tbody>
</tbody>

Dropdown
Year: 2013
Product: A
Quality:Good
Country: UK

Average score: xxxx

So if someone choose the dropdown of Year and Product only, it will only calculate the average of these two criteria only; if someone chosen 3 criteria, the averages core will narrow down 3 criteria so on and so forth.

How do I do that?

It's a little bit like

averageifs(2013, yeardata, A, productdata) or averageifs(2013, yeardata, A, productdata, UK, country), or.....

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Select the Data > Go to Insert > Pivot Table

Move Year,Product,Quality,Country to report filter and score to ∑values.

You will first see sum of score.. click on drop down > select value field setting > select Average.

HTH

I will need to be able to do this without pivot table- sorry!!!

Hi

What is the value of the dropdown if the user does not want to use a criterion? Is it a null string?

I will need to be able to do this without pivot table- sorry!!!

Why not ? This method can be more easier and effective. Also you can have pivot in the same sheet where your raw data is dumped.

it would be a text for example -Select-

Try this array formula in I7:

=AVERAGE(IF((A2:A7=I2)+(I2="-Select-"),IF((B2:B7=I3)+(I3="-Select-"),IF((C2:C7=I4)+(I4="-Select-"),IF((D2:D7=I5)+(I5="-Select-"),E2:E7)))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Try this array formula in I7:

=AVERAGE(IF((A2:A7=I2)+(I2="-Select-"),IF((B2:B7=I3)+(I3="-Select-"),IF((C2:C7=I4)+(I4="-Select-"),IF((D2:D7=I5)+(I5="-Select-"),E2:E7)))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

ABCDEFGHIJ
1YearProductQualityCountryScore Dropdown
22013AGoodUK10 Year:2013
42013CGoodUK30 Quality:Good
52013AGoodUSA40 Country:-Select-
62014DGoodUK20
8
[Book1]Sheet4

<tbody>
</tbody>

You absolutely made my day!!! Thanks very very much!
I might come back with more question, let me play round with it first. Thanks a lot!!!

Hi,

in the dropdown lists you could choose "*" to indicate "all"

=Sumproduct(--isnumber(search(H2&"@"&H3&"@"&H4&"@"&H5,A2:A10&"@"&B2:B10&"@"&C2:C10&"@"&D2:D10))*E2:E10)/sumproduct(--isnumber(search(H2&"@"&H3&"@"&H4&"@"&H5,A2:A10&"@"&B2:B10&"@"&C2:C10&"@"&D2:D10)))

could do the trick.

Regards

Edit: sorry PGC01 I did not mean to overlap

Last edited:
Thanks Canapone

Hi PGC01, is there anyway to combine iferror formula with array at all?

Replies
4
Views
449
Replies
3
Views
505
Replies
7
Views
611
Replies
1
Views
842
Replies
1
Views
675

### Forum statistics

1,207,197
Messages
6,077,016
Members
446,250
Latest member
Dontcomehereoften

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