Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Criteria for Countif | Excel Worksheet Functions | |||
COUNTIF statement with 3 criteria checks | Excel Discussion (Misc queries) | |||
COUNTIF with two criteria | Excel Worksheet Functions | |||
Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
Countif using format criteria....not number criteria? | Excel Worksheet Functions |