2016 Archives by date, by thread · List index

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

```=SUMPRODUCT(\$E\$2:\$E\$160="PO",\$B\$2:\$B\$160="Red",\$D\$2:\$D\$160="L")

This = TRUE.
There is only 1 PO and it is a Large.
If D column is set to M for medium, the value is FALSE.

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

also = TRUE.
There are Large Red jerseys without PO but it doesnt count them.

--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Fri, Jul 15, 2016 at 6:38 AM, Wade Smart <wadesmart@gmail.com> 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.

Maybe <> doesnt work on text?
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Fri, Jul 15, 2016 at 6:34 AM, Wade Smart <wadesmart@gmail.com> wrote:
```
```Sure.

B     C     D     E
Red 8     XL    IN
Red 9     L      PO
Red 10
Red 11    XL   IN
Red 12    L      OUT

First line is the column.
In column B I have all the jersey colors.
There are currently 12 colors.

Column C is the number on the jersey.

Column D is the size of the jersey. You'll
notice that Red #10 has no size. That jersey
has been lost and will need to be replaced.

Column E has one of these designations:
IN, OUT, PO, Replace, ?

So, Red #8 jersey is a XL and is currently in
inventory. Red #9 size L is PO. Red #10 has
either been lost or otherwise missing and will
be replaced (though since I copied and pasted
the data here, it currently says nothing). Red #12
is size L but is currently checked out.

--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Fri, Jul 15, 2016 at 6:24 AM, Bruce Hohl <brucehohl@gmail.com> wrote:
```
```Can you give some examples of the cell contents of column E when a PO is
present and not present.

On Fri, Jul 15, 2016 at 7:03 AM, Wade Smart <wadesmart@gmail.com> wrote:
```
```
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

On Thu, Jul 14, 2016 at 8:01 PM, Bruce Hohl <brucehohl@gmail.com> wrote:
```
```Perhaps one of these depending the content of Column E:
=COUNTIFS(E2:E160,"<>PO",  B2:B160,"Red",  D2:D160,"M")
=COUNTIFS(E2:E160,"",  B2:B160,"Red",  D2:D160,"M")
```
```
The first one produces 4 and the second one produces 0.
```
```

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