Vary the criteria in countif

[Jim Palmer]

I have a pivot table that shows the quantity of an item on hand for various locatons. I want to count the number of occurences where the quantity is less than the reorder point. I use a vlookup function to 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 reoder point.

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

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

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

Thanks in advance for your assistance.

Jim Palmer

[Dave F]

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

"Jim Palmer" wrote:

>
> I have a pivot table that shows the quantity of an item on hand for
> various locatons. I want to count the number of occurences where the
> quantity is less than the reorder point. I use a vlookup function to
> 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 reoder
> point.
>
> I can construct a countif function =countif(B2:G2,"<4") however the
> reorder point for the next item might be 10.
>
> Is there a way in which I can say countif the value is less than the
> value stored in cell H2?
>
> That is =countif(B2:G2,<h2)
>
> Thanks in advance for your assistance.
>
> Jim Palmer
>
>
> --
> Jim Palmer
> ------------------------------------------------------------------------
> Jim Palmer's Profile: http://www.excelforum.com/member.php...fo&userid=5210
> View this thread: http://www.excelforum.com/showthread...hreadid=571888
>
>

[mr_teacher]

Hi, I think the formula

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

should do what you're after?

Hope that helps

Regards

Carl

[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

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

[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