At 06:34 15/07/2016 -0500, Wade Smart wrote:
B C D E
Red 8 XL IN
Red 9 L PO
Red 10
Red 11 XL IN
Red 12 L 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: [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
