Welcome to the board!
Try this is cell B47:
=SUMPRODUCT(--(MOD(ROW(B15:B43)-ROW(B15)+1,4)=1),--(B15:B43>0))
Thanks, I have moved department at work and so you may see a few questions from me in the future. Thanks for your quick reply.
It works! I cant work out what it means (as some of those formulas are beyond my capabilities/knowledge), but it works!!!
Thanks so much
Better to invoke:
Hi Aladin,
when removing the +1 from the formulae as you have done, the answer ends up being incorrect (by 1), so I will stick to the earlier formulae.
Thanks anyways
Table A | Unit Mix: | Unit Type (Rooms) | Unpaid Rent | |
Studio | 2.0 | 1 | ||
Jr 1B | 2.5 | |||
1BR | 3.0 | 2 | ||
JR4 | 3.5 | |||
2BR | 4.0 | |||
3BR | 5.0 | |||
4BR | 6.0 | |||
Total /WA | 190 | 3 | ||
Table B | Unit Number | Unit Type (Rooms) | monthly rent | |
1A | 4.0 | $1,500.00 | ||
1B | 2.0 | $850.00 | ||
1C | 2.0 | $850.00 | ||
1D | 4.0 | $1,500.00 | ||
1E | 3.0 | $0.00 | ||
1F | 3.0 | $1,000.00 | ||
1G | 3.0 | $0.00 | ||
Question: | ||||
I have Table A and Table B. I need to calculate Unpaid Rent in Table A from Table B column Monthly Rent based on the Unit Type. Thank you very much in advance. | ||||