Vary the criteria in countif

[Jim Palmer]

I have a pivot table that shows the quantity of an item on hand fo
various locatons. I want to count the number of occurences where th
quantity is less than the reorder point. I use a vlookup function t
get the reorder point for each item.

For example

Store Location---A B C D E F Reorder Count
part #123------- 5 2 1 6 1 3 4 4

That is, the values in locations B, C, E and F are below the reode
point.

I can construct a countif function =countif(B2:G2,"<4") however th
reorder point for the next item might be 10.

Is there a way in which I can say countif the value is less than th
value stored in cell H2?

That is =countif(B2:G2,<h2)

Thanks in advance for your assistance.

Jim Palme

 

[Dave F]

IF([value you're counting for]<H2,"",COUNTIF(B2:G2)) where the square
brackets should be replaced by the value in question.

 

[mr_teacher]

Hi, I think the formula

=countif(B2:G2,"<"&h2)

should do what you're after?

Hope that helps

Regards

Car

 

[Jim Palmer]

Hi Dave

Sorry, but I don't understand your suggestion, or perhaps my example
wasn't clear.

It might be easier if I showed it down rows rather than across
columns.

Location,Qty
A,5
B,2
C,1
D,6
E,1
F,3

Suppose the values above are in columns A and B, rows 1 to 6

The "order at" is 4 stored in B7

I want the formula to say countif(B1:b6,<b7) as opposed to "<4"

When I copy it to other columns I'd like to use relative addressing.

That is, countif(c1:c6<c7)

Thanks

Jim

 

[mr_teacher]

In the example that you give here you would use the formula

=countif(B1:b6,"<"&b7)

This will update to the correct cells wherever you paste it to.

Regards

Carl

 

[Jim Palmer]

Thanks

That's exactly what I was looking for (I was very close but I didn't
have the syntax just right).

Best regards

Jim Palmer

 

[mr_teacher]

Great! Glad that you got it working and thanks for the feedback!

Regards

Carl