MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Sum If?

Posted by Jessica on November 21, 2001 10:10 AM

I am not sure if this is something I can do, but here it goes.

I have a spreadsheet that has the following columns:

Column A Column B Column C
Employee Name Hours Worked Earnings
John Doe 40 $40.00
Jane Doe $40.00

I need to find out what the total number of hours our employees worked this year. My problem is, when we have salaried employees there are no hours. Can I get Excel to enter a standard number of hours, i.e 173.33 whenever Column C has earnings but there is nothing in column B? So it automatically puts in 173.33 whenver someone earned wages but wasn't paid hourly?

I thought there was a formula whereby you can say if column B = zero and Column C is greater than $1.00 then "insert this value"?

Is this possible?


Posted by Adam -nt on November 21, 2001 10:46 AM

Column D: IF(AND(B2=0,C2>1)=true,0,Std hrs,B2)


Posted by Adam on November 21, 2001 10:48 AM

oops -lol correction

heh, too many arguments

In D column (starting with D2 I'm guessing)

=IF(AND(B2=0,C2>1)=true,Std hours,B2)

Then run your sumif over column D.