Little help with functions

S

SanchoP

New Member
Joined
Feb 7, 2014
Messages
9
Hello everyone,

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

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





























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%


[TABLE="width: 500"]
<TBODY>[TR]
[TD]X[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Many thanks in advance!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
try this one


Excel 2012
ABCD
1NameCategoryPositive / NegativeNumber
2AAxPositive103503253.7
3BBxNegative42125221.42
4CCyPositive85401512.22
5DDzPositive30600783.38
6EEyNegative32698328.06
7FFyPositive36048545.1
8GGyPositive993354.51
9HHxNegative774600.81
10IIzPositive10420490.23
11JJzPositive23557656.72
12KKxNegative1141258.86
13LLzPositive1162261.71
14MMxPositive1170530.17
15NNyNegative92860585.07
16
17
18Positive
19x1.00
20y0.00
21z0.00
Sheet2
Cell Formulas
RangeFormula
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))
 
Upvote 0
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 )

[TABLE="width: 144"]
<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl1567, width: 64, bgcolor: transparent"]x[/TD]
[TD="class: xl1567, width: 64, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl1567, width: 64, bgcolor: transparent"]AA[/TD]
[/TR]
[TR]
[TD="class: xl1567, width: 64, bgcolor: transparent"]y[/TD]
[TD="class: xl1567, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl1567, width: 64, bgcolor: transparent"]CC,FF[/TD]
[/TR]
[TR]
[TD="class: xl1567, width: 64, bgcolor: transparent"]z[/TD]
[TD="class: xl1567, width: 64, bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl1567, width: 64, bgcolor: transparent"]DD,JJ[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
EDIT: ok let me correct it, as I check each value separately and not the sum
 
Last edited:
Upvote 0
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%
Click to expand...

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
ABCD
1NameCategoryPositive / NegativeNumber
4CCyPositive85401512.22
7FFyPositive36048545.1
8GGyPositive993354.51
16
17
18Positive
19x83739027.11
20y97954729.460
21z52592953.630
Sheet4




Excel 2012
ABCD
1NameCategoryPositive / NegativeNumber
5DDzPositive30600783.38
10IIzPositive10420490.23
11JJzPositive23557656.72
13LLzPositive1162261.71
16
17
18Positive
19x83739027.11
20y97954729.460
21z52592953.630
Sheet4
 
Last edited:
Upvote 0
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
Click to expand...

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 !
 
Upvote 0
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.
 
Upvote 0
What you need is to check how many numbers need to be added so the sum will greater than 80% of the category sum.
Click to expand...

Yes , exactly

but those numbers need to be the highest ( also positive and in each category )
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
S
  • Question Question
Range Error in VBA Code
Replies
4
Views
569
NdNoviceHlp
NdNoviceHlp
D
  • Question Question
VLookup from SQL Database
Replies
1
Views
2K
jmacleary
jmacleary
T
  • Question Question
Number wheel chart
Replies
3
Views
599
shg
shg
T
  • Question Question
Help with simple function please
Replies
3
Views
555
tommygunnz
T

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top