A shorter alternative to IF formula

[Usercode]

Is there a shorter way of doing this? please.

=IF(AND(A1>0,A2>0),1,0)+IF(AND(B1>0,B2>0),1,0)+IF(AND(C1>0,C2>0),1,0)

Basically, if A1 and A2 are greater than 0 at the same time, it should be counted as "1", if not 0, and then all results in the range are summed.

My data are like this:

13 17,2 7,6
22,23 14 9
11,22 14



Thank you.

 

[sandy666]

maybe: =SUM(A1*A2>0,B1*B2>0,C1*C2>0)

 

[Usercode]

thanks, I tried but I got #VALUE ! error.

 

[sandy666]

try this =SUM(--(A1*A2>0),--(B1*B2>0),--(C1*C2>0)) or check your values in source ranges (text or numbers)

1​	2​	3​	3​	=SUM(A1*A2>0,B1*B2>0,C1*C2>0)
4​	5​	6​
0​	1​	2​	1​
3​	4​	0​

 

[Usercode]

I tried and same result unfortunately. I have excel 2016. I tried with both General and Number formatting on the data.

 

[sandy666]

Is 13 and 17 in one cell with a space between them?

try in any cell: =ISTEXT(A1)

 

[Usercode]

13 -------------- 17,2 -------------- 7,6
22,23-------------- 14 -------------- 9
11,22 -------------- -------------- 14

yes. my data has blanks cells too (without 0s).

 

[Usercode]

=istext(a1) => true for blank cells, false for cells with numbers.

 

[sandy666]

it look like this?

	A​	B​	C​	D​	E​	F​	G​	H​
1​	13​	___	___	17​	2​	___	7​	6​
2​	22​	23​	___	___	14​	___	9​	___
3​	11​	22​	___	___	___	___	14​	___

 

[Usercode]

Basically like this, but some numbers have decimals like 10,20. Sorry I don't know how to post the table like you did.