Help please with this sum.....

[lucyloos2000]

Hi there!

Would anyone be able to help? I am using this formula for the whole year bout after 10 rows it then starts doubling up the value on row one them two etc.... Is there anything I can do or is 8 it? I need about 20 active rows......



=IF(J4="",0,$F4)+IF(K4="",0,$F4)+IF(L4="",0,$F4)+IF(M4="",0,$F4)+IF(N4="",0,$F4)+IF(O4="",0,$F4)+IF(P4="",0,$F4)+IF(Q4="",0,$F4)+IF(R4="",0,$F4)+IF(S4="",0,$F4)+IF(T4="",0,$F4)+IF(U4="",0,$F4)+IF(V4="",0,$F4)+IF(W4="",0,$F4)+IF(X4="",0,$F4)+IF(Y4="",0,$F4)+IF(Z4="",0,$F4)+IF(AA4="",0,$F4)+IF(AB4="",0,$F4)+IF(AC4="",0,$F4)+IF(AD4="",0,$F4)+IF(AE4="",0,$F4)+IF(AF4="",0,$F4)+IF(AG4="",0,$F4)+IF(AH4="",0,$F4)+IF(AI4="",0,$F4)+IF(AJ4="",0,$F4)+IF(AK4="",0,$F4)+IF(AL4="",0,$F4)+IF(AM4="",0,$F4)+IF(AN4="",0,$F4)+IF(AO4="",0,$F4)+IF(AP4="",0,$F4)+IF(AQ4="",0,$F4)+IF(AR4="",0,$F4)+IF(AS4="",0,$F4)+IF(AT4="",0,$F4)+IF(AU4="",0,$F4)+IF(AV4="",0,$F4)+IF(AW4="",0,$F4)+IF(AX4="",0,$F4)+IF(AY4="",0,$F4)+IF(AZ4="",0,$F4)+IF(BA4="",0,$F4)+IF(BB4="",0,$F4)+IF(BC4="",0,$F4)+IF(BD4="",0,$F4)+IF(BE4="",0,$F4)+IF(BF4="",0,$F4)+IF(BG4="",0,$F4)+IF(BH4="",0,$F4)+IF(BI4="",0,$F4)+IF(BJ4="",0,$F4)+IF(BK4="",0,$F4)+IF(BL4="",0,$F4)+IF(BM4="",0,$F4)+IF(BN4="",0,$F4)+IF(BO4="",0,$F4)+IF(BP4="",0,$F4)+IF(BQ4="",0,$F4)+IF(BR4="",0,$F4)+IF(BS4="",0,$F4)+IF(BT4="",0,$F4)+IF(BU4="",0,$F4)+IF(BV4="",0,$F4)+IF(BW4="",0,$F4)+IF(BX4="",0,$F4)+IF(BY4="",0,$F4)+IF(BZ4="",0,$F4)+IF(CA4="",0,$F4)+IF(CB4="",0,$F4)+IF(CC4="",0,$F4)+IF(CD4="",0,$F4)+IF(CE4="",0,$F4)+IF(CF4="",0,$F4)+IF(CG4="",0,$F4)+IF(CH4="",0,$F4)+IF(CI4="",0,$F4)+IF(CJ4="",0,$F4)+IF(CK4="",0,$F4)+IF(CL4="",0,$F4)+IF(CM4="",0,$F4)+IF(CN4="",0,$F4).....CONT.....

 

[steve the fish]

Does this do the same thing?

=SUMPRODUCT(--(J4:XFD4<>""))*F4

 

[lucyloos2000]

Does this do the same thing?

=SUMPRODUCT(--(J4:XFD4<>""))*F4

Nope, afraid not.

 

[steve the fish]

It does exactly the same calculation assuming ...CONT... means and so on.

 

[lucyloos2000]

The spreadsheet is designed to log a clients daily stay at a particular building at a certain rate. Each zero represents an overnight stay. the rows work fine with the formula above but at row11 it starts to double up row 3, row 12 doubles up row 4 etc.....

I do hope you can help

[TABLE="width: 955"]
<colgroup><col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" span="3"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 3035;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;" span="6"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;" span="4"> <tbody>[TR]
[TD="class: xl63, width: 60, bgcolor: transparent"]Room[/TD]
[TD="class: xl64, width: 140, bgcolor: transparent"]Client Name[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]Date moved in[/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 74, bgcolor: transparent"]Rent Carried Over[/TD]
[TD="class: xl63, width: 90, bgcolor: transparent"]Rent per Day[/TD]
[TD="class: xl63, width: 83, bgcolor: transparent"]Rent Expected[/TD]
[TD="class: xl63, width: 72, bgcolor: transparent"]Rent Paid to Date[/TD]
[TD="class: xl65, width: 84, bgcolor: transparent"]Rent Outstanding[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]01-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]02-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]03-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]04-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]05-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]06-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]07-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]08-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]09-May[/TD]
[TD="class: xl66, width: 52, bgcolor: transparent, align: right"]10-May[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£7.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]2[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£3.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]3[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]4[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]5[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]6[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]7[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]8[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£2.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]9[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£2.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]10[/TD]
[TD="class: xl74, bgcolor: transparent"] [/TD]
[TD="class: xl73, bgcolor: white"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl69, bgcolor: transparent"]£1.00[/TD]
[TD="class: xl69, bgcolor: transparent"]£2.00[/TD]
[TD="class: xl69, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"]0[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

So when you nope afraid not what does that mean? I assume that formula is going in the cell under rent expected? I can assure you my formula does the same job as yours but is a bit shorter If it is miscalculating in your opinion then you have data in the cells (even if you cant see it).

 

[lucyloos2000]

Oh my word.......YOU ARE ACTUALLY A GOD!!!!!!!!

Thank you soooooo much. I do actually believe that you have just solved ALL my problems!! thank you soooo much!!