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

[Brian Barker]

At 06:34 15/07/2016 -0500, Wade Smart wrote:

B C D E
Red 8 XLIN
Red 9 L  PO
Red 10
Red 11XL   IN
Red 12L  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+unsubscr...@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

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

[Wade Smart]

Its been pointed out that actually state my problem
making it difficult for people help. Valid point.

I have a spread sheet that tracks jersey inventory.

Column B = Jersey Color
Column C = Number printed on the Jersey
Column D = Jersey Size
Column E = Status of the jersey
 IN, OUT, PO, Replace, ?

Column B: E, 2:160 is the range.

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

This I would think would show me all Large Red jerseys
that are not PO.

What I want to know is, for each size of jersey in
a certain color, how many do I have that are NOT PO.

I have gone through to make sure any line with PO
is actually "PO" and not " PO", " PO " or "PO ".







As for correctness of each entry, I have been going
back through all entries to make sure "PO" is just that
and not "PO " or " PO" or " PO ". I have found a three
like that.

As you said, it is exclusion of PO in a jersey color and
size that I am counting. So, for example, below I would
have  2 XL Red jerseys, 1 L Red jersey.

PO stands for Private Owned. I know from my spreadsheet
that I have x number of Red jerseys, that 1 ( in the example
data below ) is PO and is number 9 and that the rest are owned
by the league. If I have to check them in and out or have to
reorder jerseys, I will not be purchasing a shirt and put #9 on it.
So,what I want to know is, how many Red Jerseys in a size
I have that are not PO.
--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005


On Fri, Jul 15, 2016 at 7:12 AM, Wade Smart  wrote:
> Perhaps you missed this post.
> I was asked what data was in each column.
>
> As for correctness of each entry, I have been going
> back through all entries to make sure "PO" is just that
> and not "PO " or " PO" or " PO ". I have found a three
> like that.
>
> As you said, it is exclusion of PO in a jersey color and
> size that I am counting. So, for example, below I would
> have  2 XL Red jerseys, 1 L Red jersey.
>
> PO stands for Private Owned. I know from my spreadsheet
> that I have x number of Red jerseys, that 1 ( in the example
> data below ) is PO and is number 9 and that the rest are owned
> by the league. If I have to check them in and out or have to
> reorder jerseys, I will not be purchasing a shirt and put #9 on it.
> So,what I want to know is, how many Red Jerseys in a size
> I have that are not PO.
>
>
>
> B C D E
> Red 8 XLIN
> Red 9 L  PO
> Red 10
> Red 11XL   IN
> Red 12L  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:55 AM, Brian Barker  
> wrote:
>> At 04:23 15/07/2016 -0500, you wrote:
>>>
>>> On Thu, Jul 14, 2016 at 8:31 PM, Brian Barker wrote:

 =SUMPRODUCT($E$2:$E$160<>"PO",$B$2:$B$160="Red",$D$2:$D$160="M")
>>>
>>>
>>> This produces 4. I do not know how it gets that.
>>
>>
>> Hopefully because in rows 2 to 160 there are four rows for which En is not
>> exactly "PO", Bn is exactly "Red", and Dn is exactly "M". That's what it
>> does for me.
>>
>>> For Red shirts, I have 1 PO, 1 INV and 16 IN. In all shirts I have over 20
>>> PO.
>>
>>
>> It's not the number of "PO"s that matters, but how many are *not* "PO".
>>
>> I can have no idea what "INV" and "IN" are, still less which columns they
>> might be in!
>>
>> I avoided saying this before, but you have fallen into the trap of trying to
>> specify a problem by offering a formula that you know doesn't work. (To be
>> fair, you did make clear that you knew that.) Such an incorrect formula does
>> not define the problem, of course, so anyone trying to help has to guess the
>> detail you haven't provided.
>>
>> I can think of two obvious possibilities here:
>>
>> o My understanding of your problem (as clarified in my "Hopefully" sentence
>> above) is incorrect or incomplete.
>>
>> o Some of the cells that you want to match "Red" or "M" or not to match "PO"
>> actually contain something different - perhaps even just those text strings
>> with trailing spaces. Are you hoping that equating to "Red" or whatever will
>> somehow match any text content *containing* "Red"?
>>
>> Brian Barker

-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? 

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

[Wade Smart]

=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  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  wrote:
>> Sure.
>>
>> B C D E
>> Red 8 XLIN
>> Red 9 L  PO
>> Red 10
>> Red 11XL   IN
>> Red 12L  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  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  wrote:

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


 On Thu, Jul 14, 2016 at 8:01 PM, Bruce Hohl  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+unsubscr...@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

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

[Wade Smart]

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  wrote:
> Sure.
>
> B C D E
> Red 8 XLIN
> Red 9 L  PO
> Red 10
> Red 11XL   IN
> Red 12L  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  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  wrote:
>>>
>>> --
>>> Registered Linux User: #480675
>>> Registered Linux Machine: #408606
>>> Linux since June 2005
>>>
>>>
>>> On Thu, Jul 14, 2016 at 8:01 PM, Bruce Hohl  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+unsubscr...@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

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

[Wade Smart]

Sure.

B C D E
Red 8 XLIN
Red 9 L  PO
Red 10
Red 11XL   IN
Red 12L  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  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  wrote:
>>
>> --
>> Registered Linux User: #480675
>> Registered Linux Machine: #408606
>> Linux since June 2005
>>
>>
>> On Thu, Jul 14, 2016 at 8:01 PM, Bruce Hohl  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+unsubscr...@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

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

[Brian Barker]

At 19:36 14/07/2016 -0500, Wade Smart wrote:
This does not work but this is what Im thinking about. If a item in 
this range does not have PO, and is Red and is M then I want to count it.

=IF( $E$2:$E$160 <> "PO", SUMPRODUCT($B$2:$B$160="Red",$D$2:$D$160="M"))


Just:
=SUMPRODUCT($E$2:$E$160<>"PO",$B$2:$B$160="Red",$D$2:$D$160="M")

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscr...@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

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

[Bruce Hohl]

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")

On Thu, Jul 14, 2016 at 8:36 PM, Wade Smart  wrote:

> This does not work but this is what Im thinking about.
>
> If a item in this range does not have PO, and is Red and is M
> then I want to count it.
>
> =IF( $E$2:$E$160 <> "PO",
> SUMPRODUCT($B$2:$B$160="Red",$D$2:$D$160="M"))
>
> This is for tracking our league jerseys and our inventory
> to be checked out seasonally to the players. I want to know
> how many of Medium shirts Red team has.
>
>
> --
> Registered Linux User: #480675
> Registered Linux Machine: #408606
> Linux since June 2005
>
> --
> To unsubscribe e-mail to: users+unsubscr...@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
>

-- 
To unsubscribe e-mail to: users+unsubscr...@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