• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUM and COUNTIF Question

avtrout

New Member
I run perfume decanting circles and I need a formula that calculates the following:

Every original bottle has 6mls. I purchase a bottle, and decant into 6 1ml vials and sell them. Although many people buy 1ml decants, some people buy "HB" (half-bottle) decants, which equal 3ml and come in the original bottle.

I need a calculation that tells me how many bottles to buy of a particular scent, taking into account that every HB has a leftover amount of 3mls. I want to use the leftover accumulated 3mls from each HB to fill my 1ml buyers orders. If you buy a HB, you get the original bottle. So every HB is essentially a whole bottle purchase (on my end) with 3mls leftover that I sell in 1ml increments to 3 other people. (I hope this isn't confusing).

I'm including a link to the spreadsheet. I'm trying to figure out the value of H2. Here's what I have:
=SUM(COUNTIF(C2:C30,{"hb"})*{1},(IFS(J2>=7, "1", J2>=7,"2",J2<=6, "1")))

I also need the final answer to ROUNDUP. I can't purchase 1.3 bottles or something like that. So if a particular scent has only 2 1ml purchases, H2 needs to return the value of "1". Maybe I don't need to ROUNDUP. I'm not sure.


Here's a list I made that I think will help in the calculation:

1-6 DECANTS: J2<6, "1",
7-12 DECANTS: J2>6, "2",
13-18 DECANTS: J2>12, "3",
19-24 DECANTS: J2>18, "4",
25-30 DECANTS: J2>24, "5"
 

Attachments

Last edited:

Peter Bartholomew

Well-Known Member
I got the number of bottles, b, to order to come out to
= hb + CEILING( IF(d > 3*hb, d - 3*hb, 0 ), 6 ) / 6
where hb is the number of half-bottles to be supplied and d is the number of decanted vials.

The volume of perfume left once you have met your order would be
= 6*b - ( 3*hb + d )

63719
 

Peter Bartholomew

Well-Known Member
@NARAYANK991
Thanks for responding. There was something of a gap between my solution and the OP's workbook. Because, to build the table with a single formula, my names d and hb were both arrays, I was unable to use aggregation functions, MAX in particular. If d and hb referred to single cells the term
= MAX( d-3*hb, 0 )
may have been slightly more transparent way of expressing the idea that 2 half-bottle orders cannot be met simply by buying 1 bottle.
 

NARAYANK991

Excel Ninja
@NARAYANK991
Because, to build the table with a single formula, my names d and hb were both arrays, I was unable to use aggregation functions, MAX in particular. If d and hb referred to single cells the term
= MAX( d-3*hb, 0 )
may have been slightly more transparent way of expressing the idea that 2 half-bottle orders cannot be met simply by buying 1 bottle.
Hi ,

My mistake ; I didn't go through your table ; instead I just looked at your formulae.

Narayan
 
Top