Vary the criteria in countif
 

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

Vary the criteria in countif
 

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

Vary the criteria in countif
 

Hi, I think the formula

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

should do what you're after?

Hope that helps

Regards

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=571888

Vary the criteria in countif
 

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
------------------------------------------------------------------------
Jim Palmer's Profile: http://www.excelforum.com/member.php...fo&userid=5210
View this thread: http://www.excelforum.com/showthread...hreadid=571888

Vary the criteria in countif
 

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 Wrote:
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
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=571888

Vary the criteria in countif
 

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


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

Vary the criteria in countif
 

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

Regards

Carl


--
mr_teacher
------------------------------------------------------------------------
mr_teacher's Profile: http://www.excelforum.com/member.php...o&userid=34352
View this thread: http://www.excelforum.com/showthread...hreadid=571888