If Formula

[josros60]

I have a spreadsheet with 3 columns

1=Booked, 2-Tentative, 3-Cancelled


then a i have a date field in column E and room nigts column F.


for example if it is booked type an x and booked column then type date and number of nights in column E and F.

What I want is an if formula in cell 8 to add all room nights in comunt in column F if the there's an x booked column.

How would I do that?

I type this but gave an error value?

I have a spreadsheet with 3 columns

1=Booked, 2-Tentative, 3-Cancelled


then a i have a date field in column E and room nigts column F.


for example if it is booked type an x and booked column then type date and number of nights in column E and F.

What I want is an if formula in cell 8 to add all room nights in comunt in column F if the there's an x booked column.

How would I do that?

I type this but gave an error value?

[CODE]=if(ColumnBooked=X,Sum(ColumnF),"")

thank you

 

[T. Valko]

I have a spreadsheet with 3 columns

1=Booked, 2-Tentative, 3-Cancelled


then a i have a date field in column E and room nigts column F.


for example if it is booked type an x and booked column then type date and number of nights in column E and F.

What I want is an if formula in cell 8 to add all room nights in comunt in column F if the there's an x booked column.

How would I do that?

I type this but gave an error value?

I have a spreadsheet with 3 columns
 
1=Booked, 2-Tentative, 3-Cancelled
 
 
then a i have a date field in column E and room nigts column F.
 
 
for example if it is booked type an x and booked column then type date and number of nights in column E and F.
 
What I want is an if formula in cell 8 to add all room nights in comunt in column F if the there's an x booked column.
 
How would I do that?
 
I type this but gave an error value?
 
[CODE]=if(ColumnBooked=X,Sum(ColumnF),"")

thank you

Try something like this...

=SUMIF(A2:A10,"X",F2:F10)

 

[josros60]

Thank you very much worked perfectly.
another question,
I have this formula to get avaerage rate but because the filed refering to is blank I get the #Div0! error, how to get rid of this?

formula:

=SUMIF(H22:H41,"X",N22:N41)/L14)

thank you

 

[T. Valko]

Thank you very much worked perfectly.
another question,
I have this formula to get avaerage rate but because the filed refering to is blank I get the #Div0! error, how to get rid of this?

formula:

=SUMIF(H22:H41,"X",N22:N41)/L14)

thank you

Try this...

=IF(L14="","",SUMIF(H22:H41,"X",N22:N41)/L14)

 

[josros60]

Thank you

but I still getting same error #Div0!

thanks again

 

[T. Valko]

Thank you

but I still getting same error #Div0!

thanks again

Hmmm...

What's in L14?

What version of Excel are you using?

This formula will work in EVERY version of Excel:

=IF(ISERROR(SUMIF(H22:H41,"X",N22:N41)/L14),"",SUMIF(H22:H41,"X",N22:N41)/L14)

If you're using Excel 2007 or later:

=IFERROR(SUMIF(H22:H41,"X",N22:N41)/L14,"")

 

[michaelbarry]

I am an excel amateur. This post on "If" excel formulas really helped me grasp the basics. I have always "winged" it when using excel, and now with my new job responsibilities I cannot get away with it as easily. My job now requires me to create a long running sheet tracking sales goals with actual sales which matches up to the budget. It will ultimately show a running profit less expenses.