"IF" Statement with a range of logic 1

[sprog]

I have a spreadsheet with 5 columns for each day of the week. The rows are student names, and for each student I have numbers of hours that they attended a holiday program. Somedays have no value. We need to charge $10 per day for those who attended for 4 hours and less and $15 per day for those who attended for more than 4 hours. My problem is that the answer is including $10 per day for the days that people did not attend. These should not be charged.

Any help would be appreciated.

Thanks

 

[Gavona]

What formula are you using at the moment?
Can you post a sample of the data including an indication of the columns each is in?
Where there was no attendance do you have a zero or a blank?

Use Countif to check how many days greater than 0 hrs
Use Sum to total the hours
Use Sum(....) *IF(countif(....),10,15) to get your result

Whilst a single cell could contain the formula in its entirety, for clarity you will probably want the charge per hour and total hours attended in separate columns.

Gavin

 

[AnotherHiggins]

My problem is that the answer is including $10 per day for the days that people did not attend. These should not be charged.

Sounds like a great way to make some extra money!

Just kidding, of course.

From what you posted, I think all you'll need is the CountIf function.

To calculate the charge for 4 hours and less:
[tab][COLOR=blue white]=CountIf(YourRange, "<=4:00") * 10[/color]

To calculate the charge for more than 4 hours:
[tab][COLOR=blue white]=CountIf(YourRange, ">4:00") * 15[/color]

These formulas assume that YourRange is whatever range of cells you want to calculate and that you have the times stored as actual times. If your times are stored as plain numbers (4, 3.25, etc.), well,I'd advise you to change them. But failing that you can just use [COLOR=blue white]"<=4"[/color] instead. If you want to use cell references for the times (good idea if there's any change the limits might change), then you could use [COLOR=blue white]"<=" & A1[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[sprog]

Thank you very much for both of your replies. Your CountIf function has worked perfectly John. I guess this was rather simple. Sorry am a bit of an amatuer and spent many hours in the Excel help, but still not able to find the answer.

Your help is much appreciated.

Julie

 

[SkipVought]

Julie,

No need to appologize for lack of knowledge. Every last one of us are ignorant of something and all of us have been ignorant of various Excel-related features at some point.

And althought I know a lot about Excel, I am certain that there are features and techniques of which I am TOTALLY ignorant. I continue to learn, and I hope that you'll "join the club." ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}