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

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 <wadesmart@gmail.com> 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     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:55 AM, Brian Barker <b.m.barker@btinternet.com> 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+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