Setting up a calculation - i think sumproduct is required but cant work it out

[jsto7380]

Hello,


Im looking for an automated way to put a value in one of my columns (amount in 'some') so that I can complete a division (amount/amount in 'some'). This may be more clear in the table below


I think this can be achieved with a sumproduct formula to retrieve the 'amount' in 'some' but I'm having a bit of trouble getting to work...

[pre]

id	type	amount	amount in 'some'	amount/amount in 'some'
1	all	500	490	1.020408163
1	some	490	490	1
1	a	100	490	0.204081633
1	b	101	490	0.206122449
1	c	102	490	0.208163265
1	d	103	490	0.210204082
1	e	104	490	0.212244898
1	f	105	490	0.214285714
2	all	500	300	1.666666667
2	some	300	300	1
2	a	100	300	0.333333333
2	b	101	300	0.336666667
2	c	102	300	0.34
2	d	103	300	0.343333333
2	e	104	300	0.346666667
2	f	105	300	0.35
3	all	500	280	1.785714286
3	some	280	280	1
3	a	100	280	0.357142857
3	b	101	280	0.360714286
3	c	102	280	0.364285714
3	d	103	280	0.367857143
3	e	104	280	0.371428571
3	f	105	280	0.375

[/pre]
Many thanks in advance

 

[Luke M]

To verify, this is my re-statement of problem:

Need to find the values associated with "some" in the same ID group. ID, Name, and individual values are provided, and col E is a simple formula of:

=C2/D2

There is only 1 instance of "some" for each ID group.


Solution:

Formula in D2 needs to be

=SUMPRODUCT(($A$2:$A$25=A2)*($B$2:$B$25="some")*($C$2:$C$25))

 

[jsto7380]

That's it! Thank you!

 

[Luke M]

Sweet, glad I could help. =)

 

[jsto7380]

I just realised I had tried this in my data file but it comes back with a 0 value.


Any ideas why this might be?


This is the formula - $G$3 is equivalent to 'some'


=SUMPRODUCT($C$2:$C$4233=C3,$G$2:$G$4233=$G$3,$H$2:$H$4233)


#### FORGET THIS ---- I KEEP USING SOMEPRODUCT INCORRECTLY!! --- I see my mistake

 

[Luke M]

For other readers:

Sumproduct won't naturally convert a True/False array into 1's and 0's unless you force the multiplication using either extra parenthesis and "*" as seen above, or using double - symbol.

 

[Colin Legg]

Hi jsto,

If you're using Excel 2007 or later then I'd recommend using SUMIFS() instead of SUMPRODUCT() for this, because the SUMIFS() formula will be more efficient.

[pre]

=SUMIFS($H$2:$H$4233,$C$2:$C$4233,C3,$G$2:$G$4233,$G$3)

[/pre]

 

[jsto7380]

Thanks Luke M!


Thanks Colin Legg! A nice addition for the toolkit