Date: prev next · Thread: first prev next last
2016 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.