Nested OR Statement?

In simplest terms... I have two columns of numbers and I want to count the number of rows containing a number >0 in either of the two columns. I would like to create one formula to complete this calculation rather than adding a third column with an OR statement and a CountIF "true" total at the bottom (which would provide the correct answer).

I would greatly appreciate any input.

Thanks.

One way...

Data Range

	A	B	C	D
1			---	---
2	-18	-56		5
3	-16	-83
4	-4	45
5	-31	-89
6	38	22
7	15	-37
8	16	-42
9	-100	-26
10	10	-95

This formula entered in D2:

=SUMPRODUCT(SIGN((A2:A10>0)+(B2:B10>0)))

Or this:

	A	B	C	D
1
2	1	0	5	=SUM(COUNTIF(A2:A5,">0"),COUNTIF(B2:B5,">0"))
3	2	2
4	0	2
5	3	0

The COUNTIF formula is actually is rather descriptive. It counts cells with filled with numbers, and in this case that are greater than 0. It looks for values in two columns A and B and summarizes the total count of none-zero values.

Originally Posted by Caoimhe

This second formula does not work for me as I believe it provides a sum of the cells >0 rather than the sum of the rows with a cell value >0. (If that makes sense.)

No, it provides the count.

They both do the same thing Since cells with 0 would have the same count as rows with 0. Simple.

Here goes...

=SUMPRODUCT(SIGN((A2:A10>0)+(B2:B10>0)))

These expressions will return arrays of either TRUE or FALSE:

A2:A10>0 and B2:B10>0... it will look like this (T = TRUE, F = FALSE):

A2>0 = F ... B2>0 = F
A3>0 = F ... B3>0 = F
A4>0 = F ... B4>0 = T
A5>0 = F ... B5>0 = F
A6>0 = T ... B6>0 = T
A7>0 = T ... B7>0 = F
A8>0 = T ... B8>0 = F
A9>0 = F ... B9>0 = F
A10>0 = T ... B10>0 = F

These arrays are then added together. TRUE gets coerced to 1 and FALSE gets coerced to 0.

F + F = 0
F + F = 0
F + T = 1
F + F = 0
T + T = 2
T + F = 1
T + F = 1
F + F = 0
T + F = 1

This array is then passed to the SIGN function. The SIGN function returns the mathematic sign of the numbers in the array.

Negative numbers = sign -1
0 = sign 0
Positive numbers = sign 1

So:

0 = sign 0
0 = sign 0
1 = sign 1
0 = sign 0
2 = sign 1
1 = sign 1
1 = sign 1
0 = sign 0
1 = sign 1

This array is then passed to the SUMPRODUCT function and summed to get the final formula result:

=SUMPRODUCT({0;0;1;0;1;1;1;0;1}) = 5

So, to count how many rows contain at least 1 number greater than 0:

=SUMPRODUCT(SIGN((A2:A10>0)+(B2:B10>0)))

=5

Oh, I see what you saying now.

Try this

=SUM(SIGN(COUNTIF(A2:A5,">0")),COUNTIF(B2:B5,">0"))

They want to count how many rows have at least 1 number greater than 0.

Using this sample data:

Sheet1

	A	B
2	-18	-56
3	-16	-83
4	10	45
5	-31	-89

The correct result is 1.

Here's another way to do it:

=SUMPRODUCT(--(MMULT(--(A2:B5>0),{1;1})>0))

Originally Posted by AlKey

=SUM(SIGN(COUNTIF(A2:A5,">0")),COUNTIF(B2:B5,">0"))

Sorry, that doesn't quite work, although it's similar to Tony Valko's first formula (which seems to work without issue).

Yeah, that's why I usually suggest something like the 1st formula.


You're welcome. Thanks for the feedback!

=SUM(SIGN(COUNTIF(A2:A5,">0")),COUNTIF(B2:B5,">0"))

No, that doesn't do the same thing as the formulas I suggested.

If you get the correct result I think it's just a coincidence.

Good deal. Thanks for the feedback!