Sum & Count Positive And Negative Values

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,

I want to get SUM of column B, D, F, H, J, L Positive And Negative Values in columns O:P
And Count of column B, D, F, H, J, L Positive And Negative Values in columns R:S
Example Attached


Book1
ABCDEFGHIJKLMNOPQRS
1SumSumSumSumSumSumSumSumSumSumSumSumSumEMSum PositiveSum NegativeEMCount Positives SumCount Negative Sum
2Sum-155,005,00-167,80-324,60157,6020,00-320,80-113,00-40,50-107,0040,80316,40EM198,40-684,10EM24
3Sum175,00160,00-197,80-156,80-257,60-137,60120,80207,80-98,50-66,50137,80275,60EM433,60-553,90EM33
4Sum-15,00-15,0041,0041,00120,00120,0087,0087,0032,0032,00137,80137,80EM1049,80-15,00EM51
Hoja2


Thanks In Advance

Regards,
Moti
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
You can use something like this for counting, "=IF(b2>0,1,0)+if(d2>0,1,0)+IF(F2>0,1,0)+IF(H2>0,1,0)+IF(J2>0,1,0)+IF(H2>0,1,0)"
 
Last edited:
Upvote 0
Try...

1.

=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 > 0))

2.

=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 < 0))
 
Upvote 0
yky said:
You can use something like this for counting, "=IF(b2>0,1,0)+if(d2>0,1,0)+IF(F2>0,1,0)+IF(H2>0,1,0)+IF(J2>0,1,0)+IF(H2>0,1,0)"
Click to expand...
Thank you yky, your formula works perfect for both Sum & Count.


Book1
ABCDEFGHIJKLMNOPQRS
1SumSumSumSumSumSumSumSumSumSumSumSumSumEMSum PositiveSum NegativeEMCount Positives SumCount Negative Sum
2Sum-155,005,00-167,80-324,60157,6020,00-320,80-113,00-40,50-107,0040,80316,40EM198,40-684,10EM24
3Sum175,00160,00-197,80-156,80-257,60-137,60120,80207,80-98,50-66,50137,80275,60EM433,60-553,90EM33
4Sum-15,00-15,0041,0041,00120,00120,0087,0087,0032,0032,00137,80137,80EM417,80-15,00EM51
Hoja3
Cell Formulas
RangeFormula
O2=SUMIF(B2,">0",B2)+SUMIF(D2,">0",D2)+SUMIF(F2,">0",F2)+SUMIF(H2,">0",H2)+SUMIF(J2,">0",J2)+SUMIF(L2,">0",L2)
P2=SUMIF(B2,"<0",B2)+SUMIF(D2,"<0",D2)+SUMIF(F2,"<0",F2)+SUMIF(H2,"<0",H2)+SUMIF(J2,"<0",J2)+SUMIF(L2,"<0",L2)
R2=IF(B2>0,1,0)+IF(D2>0,1,0)+IF(F2>0,1,0)+IF(H2>0,1,0)+IF(J2>0,1,0)+IF(L2>0,1,0)
S2=IF(B2<0,1,0)+IF(D2<0,1,0)+IF(F2<0,1,0)+IF(H2<0,1,0)+IF(J2<0,1,0)+IF(L2<0,1,0)


Regards,
Moti
 
Upvote 0
Aladin Akyurek said:
Try...

1.

=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 > 0))

2.

=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 < 0))
Click to expand...

Thank you Aladin Akyurek, your formula works perfect with count.


Book1
ABCDEFGHIJKLMNOPQRS
1SumSumSumSumSumSumSumSumSumSumSumSumSumEMSum PositiveSum NegativeEMCount Positives SumCount Negative Sum
2Sum-155,005,00-167,80-324,60157,6020,00-320,80-113,00-40,50-107,0040,80316,40EM198,40-684,10EM24
3Sum175,00160,00-197,80-156,80-257,60-137,60120,80207,80-98,50-66,50137,80275,60EM433,60-553,90EM33
4Sum-15,00-15,0041,0041,00120,00120,0087,0087,0032,0032,00137,80137,80EM417,80-15,00EM51
Hoja3
Cell Formulas
RangeFormula
O2=SUMIF(B2,">0",B2)+SUMIF(D2,">0",D2)+SUMIF(F2,">0",F2)+SUMIF(H2,">0",H2)+SUMIF(J2,">0",J2)+SUMIF(L2,">0",L2)
P2=SUMIF(B2,"<0",B2)+SUMIF(D2,"<0",D2)+SUMIF(F2,"<0",F2)+SUMIF(H2,"<0",H2)+SUMIF(J2,"<0",J2)+SUMIF(L2,"<0",L2)
R2=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 > 0))
S2=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 < 0))



Regards,
Moti
 
Last edited:
Upvote 0
motilulla said:
Thank you Aladin Akyurek, your formula works perfect with count.
[...]
Click to expand...

I missed that you also wanted to sum:

1.1

=SUMPRODUCT(B2:L2,--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 > 0))

2.1

=SUMPRODUCT(B2:L2,--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 < 0))
 
Upvote 0
Aladin Akyurek said:
I missed that you also wanted to sum:

1.1

=SUMPRODUCT(B2:L2,--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 > 0))

2.1

=SUMPRODUCT(B2:L2,--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 < 0))
Click to expand...
Aladin Akyurek, yes now your formula works perfect with complete project


Book1
ABCDEFGHIJKLMNOPQRS
1SumSumSumSumSumSumSumSumSumSumSumSumSumEMSum PositiveSum NegativeEMCount Positives SumCount Negative Sum
2Sum-155,005,00-167,80-324,60157,6020,00-320,80-113,00-40,50-107,0040,80316,40EM198,40-684,10EM24
3Sum175,00160,00-197,80-156,80-257,60-137,60120,80207,80-98,50-66,50137,80275,60EM433,60-553,90EM33
4Sum-15,00-15,0041,0041,00120,00120,0087,0087,0032,0032,00137,80137,80EM417,80-15,00EM51
Hoja3
Cell Formulas
RangeFormula
O2=SUMPRODUCT(B2:L2,--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 > 0))
P2=SUMPRODUCT(B2:L2,--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 < 0))
R2=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 > 0))
S2=SUMPRODUCT(--(MOD(COLUMN(B2:L2)-COLUMN(B2),2)=0),--ISNUMBER(B2:L2),--(B2:L2 < 0))


Thank you so much

Regards,
Moti :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
SUMIF positive & negative values
Replies
3
Views
684
Fluff
Fluff
A
  • Question Question
Dynamic data rollup by Row - non specific criteria
Replies
8
Views
315
Andy395
A
A
  • Solved
Sum formula for Only Negative Values
Replies
3
Views
226
etaf
etaf
E
  • Solved
Summing Values
Replies
3
Views
543
LazyBug
LazyBug
J
  • Solved
Convert the first negative value to months and 0 to subsequent negative value, else convert the next negative value to months
Replies
3
Views
547
jns1087
J

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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