=COUNTIF(A1;A10.....value is greater than zero?

[fugeg70]

Hi there,

I am wondering how to finish this formula off,

I want to count the number of cells in a list that have a value that is greater than zero?

Many thanks,

Graham.

 

[b19upj]

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

 

[Aladin Akyurek]

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:

=SUMPRODUCT(--(MOD(ROW(B15:B43)-ROW(B15),4)=0),--(B15:B43>0))

if counting must start at the start cell. If counting must start at the Nth (4th cell)...

=SUMPRODUCT(--(MOD(ROW(B15:B43)-ROW(B15)+1,4)=0),--(B15:B43>0))

 

[FormR]

Better to invoke:

Noted for future reference - thanks Aladin.


@ b19upj

If there is something common in one of the other columns that can be used to determine which rows should be tested we could use a much more straight forward formula. Let us know if there is.

 

[b19upj]

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

 

[Aladin Akyurek]

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

It shouldn't...

=SUMPRODUCT(--(MOD(ROW(B15:B43)-ROW(B15),4)=0),--(B15:B43>0))

The equalty test must run as =0...

 

[Aleks14]

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.

<colgroup><col span="5"></colgroup><tbody>
</tbody>