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)

	A​	B​	C​	D​	E​
1​	13.23​	31.28​	44.18​	2​	=SUM(A1*A2>0,B1*B2>0,C1*C2>0)
2​	22​	23​

 

[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

	A​	B​	C​	D​
1​		13​	17,2	7,6
2​	22,23	14​		9​
3​		11,22	14​

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?