Formula to Count Sets of Multiple Items

[raisedwell]

I need a formula that will count and provide a total of how many total sets based on multiple cells.

SET = 1 Hat, 1 Shirt, 1 Belt, 1 Pants, 1 Pair of Socks and 1 Pair of Shoes.

In the following example, I need a formula for cell B8, that will provide how many sets. The correct result should = 1.

Thanks so much in advance.

 

[RobP]

Hi, if your SETS are always 1 each of everything, then your formula just has to be the lowest number in that range, so: =MIN(B2:B7) would do it ?
Rgds
Rob

 

[Sufiyan97]

Try

Book1
	A	B	C
1
2	Hat
3	Shirt	2
4	Belt	4
5	Pants	1
6	Socks (Pair)	3
7	Shoes (Pair)	5
8	Set(s)	1
9
Sheet1

Cell Formulas
Range	Formula
B8	B8	=MIN(B3:B7)

 

[RobP]

and if you had SETs which contained multiple items (eg. 2 Hats, 1 Shirt, 1 Belt, 2 Pants,2 socks,1 Shoes) - and you entered those references into column C, then you could use :

=ROUNDDOWN(MIN(B2/C2,B3/C3,B4/C4,B5/C5,B6/C6,B7/C7),0)

 

[raisedwell]

Thank you for the quick responses.

When I use MIN for the following scenario, it returns 1 even if one of the cells are empty.

Currently using MIN, it returns 1 but need it to return 0 if there isn't one for each set requirement.

Thoughts?

 

[RobP]

Good Catch sorry, my thoughts were wrong.. need to work on it again..

 

[Sufiyan97]

Currently using MIN, it returns 1 but need it to return 0 if there isn't one for each set requirement.

Thoughts?

Try

Book4
	A	B	C
1
2	Hat	3
3	Shirt
4	Belt	4
5	Pants	1
6	Socks (Pair)	3
7	Shoes (Pair)	5
8
9	Set(s)	0
10	Set(s)	0
11
12
Sheet1

Cell Formulas
Range	Formula
B9	B9	=IF(COUNTBLANK(B2:B7)>0,0,MIN(B2:B7))
B10	B10	=IF(COUNTIF(B2:B7,"")>0,0,MIN(B2:B7))

 

[RobP]

ok, here goes:

=LET(x, SUM(IF(B2:B7=FALSE,0,1)),IF(x<6,0,MIN(B2:B7)))

 

[raisedwell]

PERFECT!!!!! Thank you so very much.

 

[Sufiyan97]

You're welcome
Glad we could help!