If value is not present, count it.

users
#1

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.

#2

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

#3

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

I trust this helps.

Brian Barker

#4

Sure.

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.

#5

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?

#6

=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.

#7

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.

#8

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?

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.

=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