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]

so try =SUM(A1*A2>0;B1*B2>0;C1*C2>0)

according to your local settings

 

[Usercode]

I'm sorry, I don't want to give you a headache, but excel doesn't accept it, I keep getting the notification that starts with "There's a problem with this formula etc".

 

[sandy666]

sure
before I give up , could you post a link to shared excel file with representative example? (GoogleDrive, OneDrive or any similar)

in a meantime try this on numbers without decimals, just to check: =SUM(A1*A2>0,B1*B2>0,C1*C2>0)

with "my" decimals: (dot) not (comma)

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

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

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

1​

[/td][td]

13.23​

[/td][td]

31.28​

[/td][td]

44.18​

[/td][td]

2​

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

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

2​

[/td][td]

22​

[/td][td]

23​

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

 

[Usercode]

thanks for your help. here is a link to an example file: https://1drv.ms/x/s!At2JilC771VLZ71mojwEWjCR2aM?e=mNSh9a

 

[sandy666]

ok, try to change decimal to dot not comma like 10.5 not 10,5

all yellow are text here not a number

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

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

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

1​

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

13​

[/td][td=bgcolor:#FFFF00]17,2[/td][td=bgcolor:#FFFF00]7,6[/td][/tr]

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

2​

[/td][td=bgcolor:#FFFF00]22,23[/td][td]

14​

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

9​

[/td][/tr]

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

3​

[/td][td][/td][td=bgcolor:#FFFF00]11,22[/td][td]

14​

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

that is why I said about your local settings

 

[Usercode]

I'm sorry I can't change my data, not because I can't. but it has to be like that. I can use the if formula, I guess.

 

[sandy666]

maybe someone else will have an idea for a shorter formula than your IF () and with format of data like yours.
everything I tried so fast is the same length or longer

btw. this is not a good idea mixing data types

 

[Usercode]

I agree. but this task requires it. Your formula works normally, just not in my case. Thanks a lot for your time and help really.

 

[sandy666]

it can be done with longer formula but you said you want shorter than you have, so...
good luck and have a nice day

 

[Twollaston]

Do you mind if I ask why you want to shorten it?