Sum Formula Help

**lenoble22** · 05-10-2013, 11:35 AM

Hi guys - I have 2 formula questions that i'm having trouble figuring out. We are currently having a challenge at work for our wellness team that involves the number of steps a person acumulates on thier pedometer. and depending on the number of steps by a 4 person team, you get a certain number of drawings. But the catch is, everyone in the team needs to get at least 10,000 steps each week.

So the first formula I have is a sum formula for each week, this is what I have so far:
=IF(COUNT(B4:B7)=0,"",SUM(B4:B7))
I want to make the formula NOT calculate if someone in Cells B4 through B7 did not get 10000 steps that week. So if each of the 4 people get more than 10,000 then the forumula adds up all 4 cells....hopefully that makes sence

The second formula am having trouble with is total number of steps for all the weeks. The challenge is we recieve our first drawing at 80,000 steps, second at 100,000 steps, third at 140,000 and then we get an additional drawing for each additional 40,000. This is what I have so far for the formula:
=IF(H3="","",IF(H3<80000,"0 Balls",IF(H3<100000,"1 Ball",IF(H3<140000,"2 Balls",IF(H3<180000,"3 Balls",IF(H3<220000,"4 Balls",IF(H3<260000,"5 Balls",IF(H3<300000,"6 Balls","7 Balls!!"))))))))

I wanted to have the formula to continue every 40,000 steps but it must get too long to keep displaying because I get an error. I was thinking it going up to at least 10 drawings (or 10 Balls) Hopefully this makes sence to everyone

Thanks and I appreciate all the help!

**ConneXionLost** · 05-10-2013, 11:47 AM

The first formula:

Please Login or Register  to view this content.

Second formula:

Please Login or Register  to view this content.

**lenoble22** · 05-10-2013, 11:58 AM

The first formula works wonderful.

The second one will say VALUE if there is nothing entered into H3. Is there a way to hide the VALUE until something is entered into H3?

Thanks again!