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 ?

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]W[/TD]
[/TR]
[TR]
[TD]6309641[/TD]
[TD]320[/TD]
[/TR]
[TR]
[TD]6309641[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]6309641[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]6309641[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]6258924[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]6258924[/TD]
[TD]120[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"]B[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD]6309641[/TD]
[TD]820[/TD]
[/TR]
[TR]
[TD]6309641[/TD]
[TD]820[/TD]
[/TR]
[TR]
[TD]6258924[/TD]
[TD]220[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.