Sum If Column

[marvellousmaz]

I write quite a bit of vba but I really cant get my head around this one!

I have entered this in Cell L204

=SUMIF(N204:Z204,">0")

Works a treat.

I would like a vba that enters this in All rows this a value in column N

Don't know why but I just cant seem to get my head around it today!

Any help would be appreciated.

Thanks

 

[Joe4]

Welcome to the Board!

I would like a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> that enters this in All rows this a value in column N

I am not sure it is clear what you are after. Can you try explaining again in a little more detail?

 

[marvellousmaz]

Welcome to the Board!


I am not sure it is clear what you are after. Can you try explaining again in a little more detail?

Sorry.

In a nut shell I would like to add up values in N:Z and put the total in L. But only if there is a value. I don't want it to read zero.

Thanks

 

[Joe4]

How about this?

=IF(COUNT(N204:Z204)>0,SUMIF(N204:Z204,">0"),"")

 

[marvellousmaz]

How about this?

=IF(COUNT(N204:Z204)>0,SUMIF(N204:Z204,">0"),"")

Thanks for the reply.

Yeah thats similar to what I wrote with the =sumif.

Problem is it only works on one line. I want it to start checking at the top of N and keep going till the last cell this something in.

I have tried setting a range from top to last of column but thats when I start to have problems.

Thanks

 

[Joe4]

I am quite sure I am 100% clear on what you are asking, but are you asking how to anchor the start row (which would be absolute range references?

For example, if you placed this in row 2, and copied it down:

=IF(COUNT(N$2:Z2)>0,SUMIF(N$2:Z2,">0"),"")

Here is what it would look like in the next rows:

Row3:

=IF(COUNT(N$2:Z3)>0,SUMIF(N$2:Z3,">0"),"")

Row4:

=IF(COUNT(N$2:Z4)>0,SUMIF(N$2:Z4,">0"),"")

etc.

Is that what you are after?
If not, I think we will need you to explain more. Maybe work us through an actual example.

 

[marvellousmaz]

I am quite sure I am 100% clear on what you are asking, but are you asking how to anchor the start row (which would be absolute range references?

For example, if you placed this in row 2, and copied it down:

=IF(COUNT(N$2:Z2)>0,SUMIF(N$2:Z2,">0"),"")

Here is what it would look like in the next rows:

Row3:

=IF(COUNT(N$2:Z3)>0,SUMIF(N$2:Z3,">0"),"")

Row4:

=IF(COUNT(N$2:Z4)>0,SUMIF(N$2:Z4,">0"),"")

etc.

Is that what you are after?
If not, I think we will need you to explain more. Maybe work us through an actual example.

No I want each one different, so

in L1 =SUMIF(N1:Z1,">0")
in L2 =SUMIF(N2:Z2,">0")
in L3 =SUMIF(N3:Z3,">0")
in L4 =SUMIF(N4:Z4,">0")
but only if there is something in N

Is that a better explanation?

 

[Joe4]

How about:

=if(len(n1)>0,sumif(n1:z1,">0"),"")[/COLOR]

 

[marvellousmaz]

How about:

=if(len(n1)>0,sumif(n1:z1,">0"),"")

Yeah that works great for row 1. I want it to insert that on every row with a value in N and change it to add that particular row.

 

[Joe4]

Just copy the formula down. The row references will automatically adjust to reflect the row they are in.