2016 Archives by date, by thread · List index

# 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
```