How to calculate percentage if we have different rows for same resource

[Davesh Garg]

Hi,

Can someone please help in identifying the percentage formula. Table is below:

1st Criteria
Here if we are taking out the percentage of different resources then they are the sum of their unique (Actuals / NAH)
For Eg: If we require the percentage, then it should be the sum of
A. Actual of resource "A"+Actual of resource "B" + Actual of resource "C" + Actual of resource "D" + Actual of resource "E" = 32
B. NAH of resource "A" + NAH of resource "B" + NAH of resource "C" + NAH of resource "D" + NAH of resource "E" = 52

Percentage = A / B = 32/52 = 61.54%

And if we add up the columns then our percentage is not same. It is Sum of Actuals (61) / Sum of NAH (108) = 56.48%

2nd Criteria

If we need to calculate the percentage for VP whose value is X, then the correct percentage for unique resource is =32 / 52 = 61.54%
however, if we add the rows whose VP's value is X, then our calc is showing = 42/71 = 59.15%

Request if anyone can provide a formula which can be evaluated as per the criteria selected in column G2:I2 from data A1:E12

Let me know if anyone require any other information.

Thanks in advance.

	A	B	C	D	E		G	H	I	J
1	VP	Client	Name of resource	Actual	NAH		VP	Client	Name of resource	%
2	X	1	A	6	9		X			Formula
3	Y	2	A	6	9
4	X	3	A	6	9
5	X	1	B	5	8
6	Y	2	B	5	8
7	X	3	C	4	10
8	Z	4	C	4	10
9	X	5	C	4	10
10	Y	6	C	4	10
11	X	3	D	7	10
12	X	1	E	10	15

<tbody>
</tbody>

 

[Davesh Garg]

Hi,

Thanks for helping me out, however Pivot is not required for my criteria. What I require is any formula, if possible.

Thanks,
Dave

 

[oldbrewer]

VP	CLIENT	RESOURCE	HOURS
x	A	5	33					VP	x
y	B	2	34					CLIENT	a
z	A	3	35					RESOURCE	5
x	B	4	44
y	A	5	55
z	B	1	34
x	A	2	65
y	B	3	54
z	A	4	43					33
x	B	5	34
y	A	1	45
z	B	2	56
x	A	3	33					it can be done with an equation
y	B	4	34					that covers 1 box completed
z	A	5	35					any 2 completed
x	B	1	44					all 3 completed
y	A	2	55
z	B	3	34					the formula is
x	A	4	65
y	B	5	54					=IF(AND(J12="",J13=""),SUMPRODUCT((D12:D35)*(C12:C35=J14)),IF(AND(J12="",J14=""),SUMPRODUCT((D12:D35)*(B12:B35=J13)),
z	A	1	43					IF(AND(J13="",J14=""),SUMPRODUCT((D12:D35)*(A12:A35=J12)),IF(AND(J12="",J13<>"",J14<>""),SUMPRODUCT((D12:D35)*(B12:B35=J13)*(C12:C35=J14)),
x	B	2	34					IF(AND(J13="",J12<>"",J14<>""),SUMPRODUCT((D12:D35)*(A12:A35=J12)*(C12:C35=J14)),IF(AND(J14="",J12<>"",J13<>""),SUMPRODUCT((D12:D35)*(B12:B35=J13)*(A12:A35=J12)),
y	A	3	45					IF(AND(J12<>"",J13<>"",J14<>""),SUMPRODUCT((D12:D35)*(A12:A35=J12)*(B12:B35=J13)*(C12:C35=J14)),"no selection")))))))
z	B	4	56

<colgroup><col span="2"><col><col span="5"><col><col span="17"></colgroup><tbody>
</tbody>