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

Room	Client Name	Date moved in		Rent Carried Over	Rent per Day	Rent Expected	Rent Paid to Date	Rent Outstanding	01-May	02-May	03-May	04-May	05-May	06-May	07-May	08-May	09-May	10-May
1					£1.00	£7.00				0	0	0	0	0
2					£1.00	£3.00				0
3					£1.00	£1.00				0
4					£1.00	£1.00				0
5					£1.00	£1.00				0
6					£1.00	£1.00				0
7					£1.00	£1.00				0
8					£1.00	£2.00				0	0
9					£1.00	£2.00				0	0
10					£1.00	£2.00				0	0

<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> </tbody>

 

[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!!