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)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td=bgcolor:#FFFF00]

3​

[/td][td]=SUM(A1*A2>0,B1*B2>0,C1*C2>0)[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

0​

[/td][td]

1​

[/td][td]

2​

[/td][td=bgcolor:#FFFF00]

1​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td]

4​

[/td][td]

0​

[/td][td][/td][td][/td][/tr]
[/table]

 

[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?

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]

13​

[/td][td]___[/td][td]___[/td][td]

17​

[/td][td]

2​

[/td][td]___[/td][td]

7​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]

22​

[/td][td]

23​

[/td][td]___[/td][td]___[/td][td]

14​

[/td][td]___[/td][td]

9​

[/td][td]___[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]

11​

[/td][td]

22​

[/td][td]___[/td][td]___[/td][td]___[/td][td]___[/td][td]

14​

[/td][td]___[/td][/tr]
[/table]

 

[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.