If cell Q12 contains the digit 1, sum cells Q13 through Q16. otherwise do nothing (blank).

[pjw73nh]

I have banged my head against the wall for 3 hours now trying different functions. Mostly with SUMIF. While the cell will take the formula as a good formula, it will not calculate properly. It simply shows me the number in cell Q13.

=SUMIF(Q12,"1",Q13:Q16)

Also tried =SUMIF(Q12:Q12,"1",Q13:Q16)

and (just for grins) =SUMIF(Q13:Q16,"1",Q12:Q12)

and =SUMIF(Q13:Q16,"1",Q12)

I didn't expect the last two to work, but I ran out of things to try.

I tried a few iterations of COUNTIF and IF ISBLANK too.

Any thoughts?

Thanks.

P....

[Jonmo1]

Try

=IF(Q12=1,SUM(Q13:Q16),"")

[AlKey]

try this

=SUMIF(Q13:Q16,1,Q12:Q12)

[pjw73nh]

AND THE WINNER IS:........ Jonmo1.....

Thank you both for the efforts.

Now, can I interpret the statement to see if I understand it?
=IF(Q12=1,SUM(Q13:Q16),"")

If q12 contains the digit 1, sum q13-q16 else print what is between the quote marks. (in this case, nothing).

Thanks.

P

[Jonmo1]

Yep, that's pretty much it.

Glad to help.

[XOR LX]

Can I just add that it might be something of a shame if AlKey didn't also receive some rep for his creative effort? Very aesthetic, IMO.

Regards

[XOR LX]

I take it back! It's not correct!

Shame.

[AlKey]

Bummer! Could't get from the description whether the syntax was correct in formulas provided by OP

[XOR LX]

I didn't even look at the OP's initial post, to be honest! Just saw your answer and liked the originality!

Even if it wasn't quite right for this, certainly one to remember for future...

[AlKey]

Thank you XOR LX for the rep though.

[XOR LX]

Haha! You shouldn't mention it publicly since your formula didn't actually work!

The mods'll start to think I just give out rep without even thinking!

[pjw73nh]

OK Alkey, I'll give you a second chance... :-)

I need to take the same formula and *IF* there is a sum of (Q13:Q16), round it up to the nearest dollar. (no cents).

I know I can do this by using another row and cell references with the ROUNDUP function, but I an trying to keep the sheet as clean as I can.

Here is a copy of the formula that worked above without the roundup. I had to change it a little (put the "0" in as the "else" for another reason)

=IF(Q13=1,SUM(Q14:Q17),"0")

I tried several things and never got the ROUNDUP to work.

Thanks again for all your help.

P

[pjw73nh]

OK Just to show you guys I don't give up that easy, I figured it out.

=IF(Q13=1,ROUNDUP(SUM(Q14:Q17),0),"0")


Thanks again...

P.../NH

[Jonmo1]

I always like CEILING for rounding up (floor for down)

Try
=IF(Q13=1,CEILING(SUM(Q14:Q17),1),"0")

[pjw73nh]

Jonmo1, Yes ceiling worked just as well. I've not heard of that function before. But then agai, I am not doing this day and day out. Thanks.

[Jonmo1]

You're welcome.