Little help with functions

[SanchoP]

Hello everyone,

I have a problem with build a function from many if, sums and count

A	B	C	D
Name	Category	Positive / Negative	Number
AA	x	Positive	103 503 253,74</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
BB	x	Negative	42 125 221,42</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
CC	y	Positive	85 401 512,22</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
DD	z	Positive	30 600 783,38</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
EE	y	Negative	32 698 328,06</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
FF	y	Positive	36 048 545,10</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
GG	y	Positive	993 354,51</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
HH	x	Negative	774 600,81</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
II	z	Positive	10 420 490,23</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
JJ	z	Positive	23 557 656,72</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
KK	x	Negative	1 141 258,86</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
LL	z	Positive	1 162 261,71</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
MM	x	Positive	1 170 530,17</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
NN	y	Negative	92 860 585,07</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
		sum	462 458 382,00</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>

<TBODY>
</TBODY>

I would like to build a function counting quantity (countif/countifs) of "name" for each category, only "positive" which sum ( each category ) will be over 80%

E.g.

Category X = 1

Because

=sumifs($D$4:$D$17;$B$4:$B$17;B20;$C$4:$C$17;$C$4) => category "X" and "Positive"

104673783,91 * 0,8 = 83739027,128

and

103 503 253,74</SPAN> > 83739027,128

so only 1 name gave over 80%

X	1
Y
Z

<TBODY>
</TBODY>

Many thanks in advance!

 

[skorpionkz]

try this one

Excel 2012
	A	B	C	D
1	Name	Category	Positive / Negative	Number
2	AA	x	Positive	103503253.7
3	BB	x	Negative	42125221.42
4	CC	y	Positive	85401512.22
5	DD	z	Positive	30600783.38
6	EE	y	Negative	32698328.06
7	FF	y	Positive	36048545.1
8	GG	y	Positive	993354.51
9	HH	x	Negative	774600.81
10	II	z	Positive	10420490.23
11	JJ	z	Positive	23557656.72
12	KK	x	Negative	1141258.86
13	LL	z	Positive	1162261.71
14	MM	x	Positive	1170530.17
15	NN	y	Negative	92860585.07
16
17
18		Positive
19	x	1.00
20	y	0.00
21	z	0.00
Sheet2

Cell Formulas
Range	Formula
B19	=SUMPRODUCT(($D$2:$D$15>SUMIFS($D$2:$D$15,$B$2:$B$15,$A19,$C$2:$C$15,B$18)*0.8)*($B$2:$B$15=$A19)*($C$2:$C$15=$B$18))
B20	=SUMPRODUCT(($D$2:$D$15>SUMIFS($D$2:$D$15,$B$2:$B$15,$A20,$C$2:$C$15,B$18)*0.8)*($B$2:$B$15=$A20)*($C$2:$C$15=$B$18))
B21	=SUMPRODUCT(($D$2:$D$15>SUMIFS($D$2:$D$15,$B$2:$B$15,$A21,$C$2:$C$15,B$18)*0.8)*($B$2:$B$15=$A21)*($C$2:$C$15=$B$18))

 

[SanchoP]

Thank you for your reply however it is not enought for me .
Your counting gives in y 0 and z also 0

I will need to get y = 2 because CC ( Positive and the highest in category y- 85401512.22 ) and FF ( also positive and 2end highest in category y - 36048545.1 ) . Sum of CC and FF = 121450057,32 . This is bigger then sum of y category * 0,8

121450057,32 > 97954729,464 ( 122443411,83*0,8 )

x	1	AA
y	2	CC,FF
z	2	DD,JJ

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

 

[skorpionkz]

EDIT: ok let me correct it, as I check each value separately and not the sum

 

[skorpionkz]

Ok don't understand example from post#1 then

Category X = 1

Because

=sumifs($D$4:$D$17;$B$4:$B$17;B20;$C$4:$C$17;$C$4) => category "X" and "Positive"

104673783,91 * 0,8 = 83739027,128

and

103 503 253,74 > 83739027,128

so only 1 name gave over 80%

you check the 80% of sum of all Positive X
but then why you count only first value against this 80%.

based on this example formula works correct as each of numbers for all Positive Z and Y are less than 80% of category sum

Excel 2012
	A	B	C	D
1	Name	Category	Positive / Negative	Number
4	CC	y	Positive	85401512.22
7	FF	y	Positive	36048545.1
8	GG	y	Positive	993354.51
16
17
18		Positive
19	x	83739027.1		1
20	y	97954729.46		0
21	z	52592953.63		0
Sheet4

Excel 2012
	A	B	C	D
1	Name	Category	Positive / Negative	Number
5	DD	z	Positive	30600783.38
10	II	z	Positive	10420490.23
11	JJ	z	Positive	23557656.72
13	LL	z	Positive	1162261.71
16
17
18		Positive
19	x	83739027.1		1
20	y	97954729.46		0
21	z	52592953.63		0
Sheet4

 

[SanchoP]

Sorry for misguide you in post 1

The formula is working correct

based on this example formula works correct as each of numbers for all Positive Z and Y are less than 80% of category sum

but I want a formula counting sum of Positive X ( this is a basic sum ) and multiplay by 0,8 will give a 80% of Positive and X.
Than I need to find how many the highest names are more then basic sum*0,8

E.g.

Y=2 ( CC and FF )
CC ( Positive and the highest in category y- 85401512.22 ) and FF ( also positive and 2end highest in category y - 36048545.1 ) . Sum of CC and FF = 121450057,32 . This is bigger then sum of y category * 0,8

121450057,32 (121450057,32=85401512,22+36048545,1) > 97954729,464 ( 122443411,83*0,8 )


Reasuming I need a sum more then 80% not a less than 80%.


Realy appriciate your help

Thank YOU !

 

[skorpionkz]

So let me check if I understood you correctly.

You actually don't need to check how many numbers in particular category are greater than 80% of it sum.
What you need is to check how many numbers need to be added so the sum will greater than 80% of the category sum.

 

[SanchoP]

What you need is to check how many numbers need to be added so the sum will greater than 80% of the category sum.

Yes , exactly

but those numbers need to be the highest ( also positive and in each category )

 

[skorpionkz]

Hmm this is not so "little"

To be honest I am not sure how to make formula to do what you requested. Will try of course but no promises (I can do this with VBA though). Maybe some one more experienced will able to help.

 

[SanchoP]

Any further help will be much appreciated.