Combining the sum and rounddown function

[UFGATORS]

Hello, I'm having some difficulty trying to use the SUM and ROUNDDOWN function. I have the following formula "=IF(ISTEXT(D3),IF(D3="Basic",SUM(L3:AP3)+COUNTIF(L3:AP3,"NA"),""),"")". This formula looks in column D to see the rate, then sums the entries in cells L:AP. In some cases I need to enter .5 in L:AP so I need to add the ROUNDDOWN function to be able to count full days. Thanks for the help.

 

[JoeMo]

maybe:
change this
SUM(L3:AP3)+COUNTIF(L3:AP3,"NA")
to this
ROUNDOWN(SUM(L3:AP3)+COUNTIF(L3:AP3,"NA"),0)

 

[UFGATORS]

That did not work, the formula has to have =IF(ISTEXT(D3),IF(D3="Basic",SUM(L3:AP3) or something else that looks in cell D3 for the rate first before it rounds and sums L3:AP3

 

[Marcelo Branco]

It's not clear what you need. Could you provide an example? A small data sample along with expected result would be helpful.

M.

 

[UFGATORS]

Hi Marcelo, you have helped my in the past. Let me try explaining what I need. In cell D3 there is a rate, "Basic" or "SC", in range L2:AP2 I would enter a 1 for a day of care or .5 for a half day care, the sum of L3:AP3 would be entered into cell AQ3 if D3="Basic". So if I had 1, 1, 1, 1, and .5 I need AQ3 to SUM the total to the nearest full day which would be 4 using the example. If L3:AP3 had the following entries 1,1,.5 the total in AQ3 would be 2 or 1,1,.5,.5 would = 3. I hope this I clearer. Thank you.

 

[Marcelo Branco]

Hi Marcelo, you have helped my in the past. Let me try explaining what I need. In cell D3 there is a rate, "Basic" or "SC", in range L2:AP2 I would enter a 1 for a day of care or .5 for a half day care, the sum of L3:AP3 would be entered into cell AQ3 if D3="Basic". So if I had 1, 1, 1, 1, and .5 I need AQ3 to SUM the total to the nearest full day which would be 4 using the example. If L3:AP3 had the following entries 1,1,.5 the total in AQ3 would be 2 or 1,1,.5,.5 would = 3. I hope this I clearer. Thank you.

Do you want to sum L2:AP2 or L3:AP3? If the later, maybe
=IF(D3="Basic",ROUNDDOWN(SUM(L3:AP3),0),"")

M.

 

[Scott Huish]

Or you could just use INT:

=IF(D3="Basic",INT(SUM(L3:AP3)),"")

 

[UFGATORS]

Thank you Marcelo, sorry for the typo, it was L3:AP3. I swear I tried what you provided but it did not work. Apparently I did not have it the same way. Thank You.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.
ps: the formula provided by Scott in post 7 seems simpler and should work as well.