Count Formula

[kbendelac]

I am looking for a formula to identify if an item is out of stock, and if so, to count if item is on order in various stockrooms.

If O4=false, then count if X4, AJ4, AV4, BJ4 is greater than o, (in other words on order)

I'd like to see a result such as 3/4 or the item is on order for 3 out of 4 stockrooms.

Is this possible?

 

[mart37]

=SUMPRODUCT((X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0))

 

[jtakw]

Hi,

Think it'll work without invoking any Functions.
Take your pick:
A1 shows how many stockrooms have your item on order, only the number of stockrooms.
A2 shows, same as above, with "/4" added.
A3 shows, same as above, with extra wording (phrase).

I don't have any values in the subject cells, that's why the result is 0.

Book3.xlsx
	A
1	0
2	0/4
3	On order for 0 out of 4 stockrooms
Sheet842

Cell Formulas
Range	Formula
A1	A1	=(X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0)
A2	A2	=(X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0)&"/4"
A3	A3	="On order for "&(X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0)&" out of 4 stockrooms"

 

[kbendelac]

Hi,

Think it'll work without invoking any Functions.
Take your pick:
A1 shows how many stockrooms have your item on order, only the number of stockrooms.
A2 shows, same as above, with "/4" added.
A3 shows, same as above, with extra wording (phrase).

I don't have any values in the subject cells, that's why the result is 0.

Book3.xlsx
	A
1	0
2	0/4
3	On order for 0 out of 4 stockrooms
Sheet842

Cell Formulas
Range	Formula
A1	A1	=(X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0)
A2	A2	=(X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0)&"/4"
A3	A3	="On order for "&(X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0)&" out of 4 stockrooms"

This is perfect! Thank you!

 

[kbendelac]

=SUMPRODUCT((X4>0)+(AJ4>0)+(AV4>0)+(BJ4>0))

Thank you!

 

[jtakw]

You're welcome, thanks for the feedback.