how do I count cells with a value >0 in multiple worksheets

[Guest]

 

[Guest]

When using: =COUNTIF(Sheet1:Sheet3!B1,">0") I get a "Value" error

B1 on sheet1 has value 1
B1 on sheet2 has value 1
B1 on sheet3 has no value

I want the cel count to = 2

 

[Frank Kabel]

Hi
COUNTIF can't work on multiple sheets Try the following formula
instead:
=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet" & ROW(1:3) & "'!B1),">0"))

 

[Aladin Akyurek]

If you just want to sum B1 across Sheet1 to Sheet3...

=SUM(Sheet1:Sheet3!B1)

If you just want to count numbers B1 houses across Sheet1 to Sheet3...

=COUNT(Sheet1:Sheet3!B1)

If you want to sum B1 when B1 > 0 (exluding negative values) across Sheet1
to Sheet3...

[1] If you have the morefunc.xll add-in installed:

For Conditional Summing:

=SUMPRODUCT(--(THREED(Sheet1:Sheet3!B1)>0),THREED(Sheet1:Sheet3!B1))

For Conditional Counting:

=COUNTIF.3D(Sheet1:Sheet3!B1,">0")

[2] If you need to do it without such an add-in:

For Conditional Summing:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B1"),">0"))

For Conditional Counting:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B1"),">0"))

where Sheets refers to a range housing the relevant sheet names, that is,
Sheet1, Sheet2, and Sheet3.