how to sum logical array? if I use =SUM(TRUE,FALSE,TRUE) it give 2

Now, Let the cell A1 is abcdea & I used =IFERROR(MID(A1,ROW(A1:A11),1)=MID(A1,LEN(A1)+1-ROW(A1:A11),1),0) and then checked by F9 it gives {TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;0;0;0;0;0} now if I use =SUM(IFERROR(MID(A1,ROW(A1:A11),1)=MID(A1,LEN(A1)+1-ROW(A1:A11),1),0)) it gives zero , but I expected 2. so is there any way to SUM logical arrays? Thanks! regards,


The -- will convert the Boolean into their integer equivalents. so =SUM(--IFERROR(MID(A1,ROW(A1:A11),1)=MID(A1,LEN(A1)+1-ROW(A1:A11),1),0)) gives expected solution.

| improve this answer | |

Libreoffice: -- will not work as indicated.

The problem comes from attempting math on boolean values. In practice this is equal to TRUE+TRUE where the result cannot be more than a single TRUE. i.e. two true doesn't make double TRUE in boolean.

Ponder on =(1=1)+(2=2) if you didn't follow ^ that.

=SUM( IF( MID(A1;ROW(A1:A11);1)=MID(A1;LEN(A1)+1-ROW(A1:A11);1) ;1;0) )
should work in both Excel and anything else.

The same should be the case for:
=SUM( IFERROR( (MID(A1;ROW(A1:A11);1)=MID(A1;LEN(A1)+1-ROW(A1:A11);1))*1 ;0) )

Note the ;'s, replace these with , depending on your locale/region

| improve this answer | |
  • Hi Hannu, I can not understand how ';' works ? – Hemant Rupani Mar 22 '15 at 5:31
  • Check "List separator" in Control panel, Regional settings. (I'm in Sweden, you will see ; if you set Sweden as country ;-) – Hannu Mar 22 '15 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.