# Average of the Sumif result ?

#### WalterWhite

##### New Member
Hi everyone there is my problem; i have 2 different excel file. First one is BW, second one BI. I was using SUMIF formula for my second excel. it was fine until repeating numbers came up. As you can see its summing for each repeating numbers and i don't want that. I want the average of the sum. For example in my second table would be 410 for each number is ideal for me. How can i do that ?

 B W 6309641 320 6309641 350 6309641 60 6309641 90 6258924 100 6258924 120

<tbody>
</tbody>

 B I 6309641 820 6309641 820 6258924 220

<tbody>
</tbody>

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Hi, I am a bit confused. Why would 410 be the average when 820 is the sum of 4 cells? Are you averaging the number by the amount of times it appears in the second table?

Yes. Thats correct. I need averaging the number by the amount of times it appears in the second table

Do your sumifs then divide by the number of times the number appears in table 2. eg:

=SUMIFS(Sheet1!W:W,Sheet1!B:B,B1)/COUNTIFS(B:B,B1)

Thank you so much. I've been dealing with this 2 day.

Replies
3
Views
104
Replies
5
Views
102
Replies
3
Views
173
Replies
3
Views
199
Replies
4
Views
537

1,203,542
Messages
6,056,012
Members
444,840
Latest member
RazzelDazel

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