Home

Returning FALSE when adding up a series of cells in which one of the cells has a false

%3CLINGO-SUB%20id%3D%22lingo-sub-731861%22%20slang%3D%22en-US%22%3EReturning%20FALSE%20when%20adding%20up%20a%20series%20of%20cells%20in%20which%20one%20of%20the%20cells%20has%20a%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731861%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20do%20something%20fairly%20simple.%20I%20want%20to%20add%20a%20number%20of%20cells%20that%20have%20numbers%20in%20them%20but%20if%20one%20of%20the%20cells%20has%20a%20false%20in%20it%20then%20I%20want%20a%20FALSE%20returned.%20In%20other%20words%20I%20don't%20want%20Excel%20to%20add%20the%20other%20numbers%20and%20ignore%20the%20word%20false%20in%20one%20of%20the%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-731861%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733234%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20FALSE%20when%20adding%20up%20a%20series%20of%20cells%20in%20which%20one%20of%20the%20cells%20has%20a%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369401%22%20target%3D%22_blank%22%3E%40jawick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20that%20sample%2C%20if%20that's%20sum%20(on%20the%20left)%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20346px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121816i0E32869E50C9556C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhen%3C%2FP%3E%0A%3CPRE%3E%3DIF(SUMPRODUCT(ISLOGICAL(%24B%242%3A%24B%248)*1)%2CFALSE%2CSUM(B2%3AB8))%3C%2FPRE%3E%0A%3CP%3Eif%20running%20total%20(on%20the%20right)%3C%2FP%3E%0A%3CPRE%3E%3DG2%2BF3*(1-SUMPRODUCT(ISLOGICAL(%24F%242%3A%24F3)*1))%3C%2FPRE%3E%0A%3CP%3EI%20assume%20there%20is%20no%20TRUE%2C%20it'll%20be%20the%20same%20trigger%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733297%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20FALSE%20when%20adding%20up%20a%20series%20of%20cells%20in%20which%20one%20of%20the%20cells%20has%20a%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733297%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20From%20your%20example%2C%20if%20all%20cells%20from%20B2%20to%20B8%20are%20numbers%20then%20I%20would%20like%20the%20sum%20of%20those%20numbers%20displayed.%20If%20there%20is%20a%20FALSE%20in%20anyone%20of%20the%20cells%20from%20B2%20to%20B8%20then%20I%20would%20like%20the%20word%20FALSE%20displayed.%20I%20don't%20know%20if%20your%20formula%20encapsulates%20what%20I%20need%2C%20partly%20because%20the%20cells%20that%20I%20would%20like%20to%20reference%20are%20not%20consecutive%2C%20i.e.%20A47%2BA87%2BA93%2BA99%2BA105%2BA111.%3C%2FP%3E%3CP%3ETIA%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733330%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20FALSE%20when%20adding%20up%20a%20series%20of%20cells%20in%20which%20one%20of%20the%20cells%20has%20a%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369401%22%20target%3D%22_blank%22%3E%40jawick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhen%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(ISLOGICAL(A47)%2BISLOGICAL(A87)%2BISLOGICAL(A93)%2BISLOGICAL(A99)%2BISLOGICAL(A105)%2BISLOGICAL(A111)%2CFALSE%2CA47%2BA87%2BA93%2BA99%2BA105%2BA111)%3C%2FPRE%3E%0A%3CP%3ESheet2%20attached%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735064%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20FALSE%20when%20adding%20up%20a%20series%20of%20cells%20in%20which%20one%20of%20the%20cells%20has%20a%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735064%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20worked!%20Thank%20you%20much%20Sergei%2C%20by%20the%20way%20is%20there%20a%20way%20to%20incorporate%20ISLOGICAL%20into%20an%20IF%2C%20AND%20formula%3F%20For%20instance%20if%20A42%3D%22a%22%20and%20a%20series%20of%20cells%20are%20logical%20then%20out-put%20the%20addition%20or%20multiplication%20of%20a%20series%20of%20cells%3F%20I%20am%20guessing%20here%20but%20something%20like%20this%3A%20IF(AND(A42%3D%22a%22%2C%20ISLOGICAL(A56)*ISLOGICAL(A62)*ISLOGICAL(A70)%2CFALSE%2CA56*A62*A70)%3C%2FP%3E%3CP%3ETIA%20%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735247%22%20slang%3D%22en-US%22%3ERe%3A%20Returning%20FALSE%20when%20adding%20up%20a%20series%20of%20cells%20in%20which%20one%20of%20the%20cells%20has%20a%20false%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735247%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369401%22%20target%3D%22_blank%22%3E%40jawick%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20this%20formula%3C%2FP%3E%0A%3CPRE%3E%3DIF((A42%3D%22a%22)*ISLOGICAL(A56)*ISLOGICAL(A62)*ISLOGICAL(A70)%2CFALSE%2CA56*A62*A70)%3C%2FPRE%3E%0A%3CP%3Eyou%20have%20FALSE%20if%20A42%20is%20%22a%22%20and%20each%20of%20the%20cells%20is%20TRUE%20or%20FALSE.%20If%20you%20have%20at%20least%20one%20numeric%20equivalent%20of%20TRUE%20or%20FALSE%26nbsp%3B%20in%20these%20cells%20(i.e.%20non-zero%20number%20or%20zero)%20formula%20returns%200%20instead%20of%20FALSE.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
jawick
New Contributor

Hello,

I am trying to do something fairly simple. I want to add a number of cells that have numbers in them but if one of the cells has a false in it then I want a FALSE returned. In other words I don't want Excel to add the other numbers and ignore the word false in one of the cells.

5 Replies

@jawick 

For that sample, if that's sum (on the left)

image.png

when

=IF(SUMPRODUCT(ISLOGICAL($B$2:$B$8)*1),FALSE,SUM(B2:B8))

if running total (on the right)

=G2+F3*(1-SUMPRODUCT(ISLOGICAL($F$2:$F3)*1))

I assume there is no TRUE, it'll be the same trigger

@Sergei Baklan 

Thank you. From your example, if all cells from B2 to B8 are numbers then I would like the sum of those numbers displayed. If there is a FALSE in anyone of the cells from B2 to B8 then I would like the word FALSE displayed. I don't know if your formula encapsulates what I need, partly because the cells that I would like to reference are not consecutive, i.e. A47+A87+A93+A99+A105+A111.

TIA

@jawick 

When like

=IF(ISLOGICAL(A47)+ISLOGICAL(A87)+ISLOGICAL(A93)+ISLOGICAL(A99)+ISLOGICAL(A105)+ISLOGICAL(A111),FALSE,A47+A87+A93+A99+A105+A111)

Sheet2 attached

 

That worked! Thank you much Sergei, by the way is there a way to incorporate ISLOGICAL into an IF, AND formula? For instance if A42="a" and a series of cells are logical then out-put the addition or multiplication of a series of cells? I am guessing here but something like this: IF(AND(A42="a", ISLOGICAL(A56)*ISLOGICAL(A62)*ISLOGICAL(A70),FALSE,A56*A62*A70)

TIA  

@Sergei Baklan 

@jawick 

With this formula

=IF((A42="a")*ISLOGICAL(A56)*ISLOGICAL(A62)*ISLOGICAL(A70),FALSE,A56*A62*A70)

you have FALSE if A42 is "a" and each of the cells is TRUE or FALSE. If you have at least one numeric equivalent of TRUE or FALSE  in these cells (i.e. non-zero number or zero) formula returns 0 instead of FALSE.

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies