Average of the Sumif result ?

[WalterWhite]

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>

 

[KennyGreens]

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?

 

[WalterWhite]

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

 

[steve the fish]

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)

 

[WalterWhite]

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