Re: [libreoffice-users] If value is not present, count it.

At 06:34 15/07/2016 -0500, Wade Smart wrote:
> B     C     D     E
> Red 8     XL    IN
> Red 9     L      PO
> Red 10
> Red 11    XL   IN
> Red 12    L      OUT

With this data, my earlier suggested formula of
=SUMPRODUCT($E$2:$E$160<>"PO",$B$2:$B$160="Red",$D$2:$D$160="M")
gives the result zero, which appears to be what you need.

Job done?

At 06:38 15/07/2016 -0500, Wade Smart wrote:
> 4 = the total number of M shirts for Red.
> So this
> =SUMPRODUCT($E$2:$E$160<>"PO",$B$2:$B$160="Red",$D$2:$D$160="M")
> is not matching the first section.

There are no Ms at all in your sample data. No-one can see four Red 
Ms and no-one can see if they have "PO" or not, so no-one can guess 
what you are doing wrong.

> Maybe <> doesn't work on text?

Of course it does. My old friend William of Ockham tells me you 
shouldn't think this way.

At 06:52 15/07/2016 -0500, Wade Smart wrote:
> =SUMPRODUCT($E$2:$E$160="PO",$B$2:$B$160="Red",$D$2:$D$160="L")
> This = TRUE.

No, it cannot be TRUE, since SUMPRODUCT() returns a numerical value, 
not a Boolean. Have you - inappropriately - set the format for the 
result cell to "Boolean value"?

> There is only 1 PO and it is a Large.

"L" is not the same as "Large", of course.

> If D column is set to M for medium, the value is FALSE.

Again, SUMPRODUCT() cannot give the result FALSE.

> This one
> =SUMPRODUCT($E$2:$E$160<>"PO",$B$2:$B$160="Red",$D$2:$D$160="L")
> also = TRUE.

Set the format of the result cell to a numeric one.

> There are Large Red jerseys without PO but it doesn't count them.

I cannot see those.

You sent me a private message, which I therefore replied to 
privately. I suggested there that one problem could be that some of 
your cells that appear to be "OP" or "Red" or whatever actually 
contain something different - perhaps just having trailing spaces.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted