Count and sum?

[nparsons75]

Hi, I have two tables, in two seperate worksheets. I will refer to them as table and table 2.

In table one I have a reference number in col A and a value (%) which i would like to show in col B.

Table 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reference[/TD]
[TD]% Progress[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In Table 2 I have a unique reference number in col A, in col B I have a number that will match that of one of the references in col A of table 1. In col C I have a another % progress value.

Table 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unique Reference[/TD]
[TD]Reference (to Table 1)[/TD]
[TD]% Progress[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]2[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]2[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2[/TD]
[TD]5%[/TD]
[/TR]
</tbody>[/TABLE]


From the two tables in seperate worksheets i need to calculate the total % using the reference number. So as an example, table one, when the correct formula is applied will look like the below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Reference[/TD]
[TD]% Progress[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]50%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]


So, Table 1 needs to look at table 2, it will look to see if any reference in col B of table 2 are the same as the reference in table 1. If it is it will add up the sum of all those that match and return a result showing the sum of all percentages matching the reference as in example above.

I hope that makes some kind of sense and I thank you in advance for your support.

 

[njimack]

Excel Workbook
	A	B
1	Reference	Progress
2	1	10%
3	2	50%
4	3	0%
5	4	0%
Sheet1

 

[Aladin Akyurek]

Book1
	A	B	C
1	Unique Reference	Reference (to Table 1)	% Progress
2	10	2	15%
3	11	2	30%
4	12	1	10%
5	13	2	5%
Sheet2

Book1
	A	B
1	Reference	% Progress
2	1	0.1
3	2	0.5
4	3	0
5	4	0
Sheet1

In B2 enter and copy down:

=SUMIFS(Sheet2!$C$2:$C$5,Sheet2!$B$2:$B$5,A2)

 

[nparsons75]

@njimack, in the example i only show 4 rows, however there are currently 200+ with more to come? Is there a way to replicate the formula down?

The current formula I use is is a combination of sum if and count if. It used to work but I cant get it to work anymore for some reason...

=IFERROR(SUMIF(Table1[@PIC],Table1[LEAD])/COUNTIFS(Table1[@LEAD],A15),0)

 

[nparsons75]

Hi, this formula has worked thank you. The only issue I have is that is showing the total value (Which is what i asked for by mistake....) The result I need is at what progress the reference is at in sheet 1. So,

Reference 2 is equal to 50%, however this is the total. If all were at 100% that would then so 300%. I need it to show 100%. I think it needs to be the average. So, if the values were 100%, 100% and 1%, the total value would be 67%. I think this is why I originally had the countif as part of the formula. So it would count the number of references that matched, sum them and average them.

This was the original formula that i can no longer get to work....

=IFERROR(SUMIF(Table1[@PIC],Table1[LEAD])/COUNTIFS(Table1[@LEAD],A15),0)

 

[Aladin Akyurek]

An average for reference 2 won't be 50%. You can of course a sum above 100% report as 100%. Any comment?