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 <[email protected]> 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 <[email protected]>
> 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: [email protected]
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
