Excel formula issue when used TRUE/FALSE as a criteria

[Nelboy]

Good Morning

I am having a small issue with the following formula

=SUMIFS('DVP Settlement Report'!P:P,'DVP Settlement Report'!E:E,"Sell",'DVP Settlement Report'!V:V,FALSE,'DVP Settlement Report'!R:R,C7)

This formula returns a zero value (as it does not recognise the FALSE value) (I have tried it with and without brackets)

now if I try the formula (and manually changing the FALSE to unsettled as an example)

=SUMIFS('DVP Settlement Report'!P:P,'DVP Settlement Report'!E:E,"Sell",'DVP Settlement Report'!V:V,"unsettled",'DVP Settlement Report'!R:R,C7)

this returns the desired result

This is not ideal as I have a large reference pool with TRUE/FALSE values in column V

I would be grateful if anyone can tell me what I am doing wrong.

thanks in advance

 

[Gerald Higgins]

Are the entries in column V text strings that contain the words "TRUE" and "FALSE" or are they Excel logical True / False values.
If they are text strings, then Excel does not usually recognise that text TRUE is the same as logical TRUE.
To test this, try =istext(V1) on a value in column V.
If you get TRUE, then col V is a text string.
In which case use "FALSE" in your original formula, instead of FALSE

 

[jasonb75]

This formula returns a zero value (as it does not recognise the FALSE value) (I have tried it with and without brackets)

Brackets, or double quotes? You could be falling into the FALSE or "FALSE" trap.

 

[Nelboy]

Hi Gerald - thanks for responding - they are in fact Excel logic TRUE/FALSE. which must be the problem

 

[Gerald Higgins]

If they really are Excel logical True / False values, then your original formula should work.

I tested it on a simplified set of data, and SUMIFS is capable of dealing with logical FALSE values correctly.

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]2[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]3[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]4[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]5[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Using this formula
=SUMIFS(B1:B10,A1:A10,"a",C1:C10,FALSE)
Where the values in C1:C3 are logical FALSE, and the values in C4:C5 are text FALSE
Returns the result 4, as expected.

And playing around with the source data results in changes to the SUMIFS result entirely as expected.