Average of the Sumif result ?

WalterWhite

New Member
Joined
Feb 6, 2019
Messages
3
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 ?

BW
6309641320
6309641350
630964160
630964190
6258924100
6258924120

<tbody>
</tbody>

BI
6309641820
6309641820
6258924220

<tbody>
</tbody>
 

KennyGreens

Board Regular
Joined
Aug 8, 2018
Messages
142
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

New Member
Joined
Feb 6, 2019
Messages
3
Yes. Thats correct. I need averaging the number by the amount of times it appears in the second table
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,829
Office Version
365
Platform
Windows
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)
 

Forum statistics

Threads
1,085,542
Messages
5,384,327
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top