# "IF" formula help needed for Numbers



## tilt (Mar 3, 2005)

Hi,

I need conditional formula help in Numbers. I am completely lost and would appreciate help here please. I am listening to PMI podcasts of different durations to earn PDUs. I am tracking the title and duration of each podcast. I need to enter the date I listened to each podcast, and add up all the durations I have listed to in one cell.

Column D is of type Duration (Hours and Minutes) of each podcast title, Column E is predominantly of type Date (that I listened to the podcast), except for the top cell which is of type Duration (Hours and Minutes). This top cell needs to hold the sum of the durations of all the podcasts I have listened to.

Basically what i want to achieve is this:

When I listen to a podcast, I shall enter the date in the appropriate cell in Column E. The moment I enter the date (meaning, if that cell is not blank), the top cell in that column should add the duration of that particular podcast to the existing value in that cell.

Is this even possible in Numbers? Have I described my issue clearly or have I made a mess of explaining?

Thanks and cheers


----------



## BReligion (Jun 21, 2006)

I can't Help you in numbers (don't have it), but an easy way in excel (that might be portable to Numbers) would be to add a column that you use as your evaluated place holders for duration. Hide that column and sum based on it. 
In excel there is a function for checking blank cells ISBLANK(). Not sure if thats in numbers. Checking for "" is tricky the way excel handles blanks (if i remember right).... You would have to play with it to see how numbers handles Blanks (see if it evaluates as 0)

Basic setup would look like this: 
(see attachment, wouldn't display properly)


Summary Column
=SUM(d2:d5) (in this case would return 2:15)

oh and also i had formatted the duration columns to custom h:mm format.
Again, sorry not a numbers guy, but that should give you a fundamental idea.

BReligion


----------



## Rob (Sep 14, 2002)

It sounds like you could use the sumif function for that.

iWork '09: SUMIF


----------



## eMacMan (Nov 27, 2006)

As near as I can tell Numbers does not have the ISBLANK function. A royal PITA which forced me to rewrite a number of AppleWorks Spreadsheets when I shifted over to Numbers.

NOTE: I have not included any links in this post. If any links do appear, they are of the bastard variety.


----------



## BReligion (Jun 21, 2006)

Rob said:


> It sounds like you could use the sumif function for that.
> 
> iWork '09: SUMIF


Yup SUMIF should work too... Forgot about that handy function. I am used to doing it the long way. Normally using that hidden column for the basis of a bunch of calculations (and its been 9 months since i touched a spreadsheet).



eMacMan said:


> As near as I can tell Numbers does not have the ISBLANK function. A royal PITA which forced me to rewrite a number of AppleWorks Spreadsheets when I shifted over to Numbers.


Yeah i remember there being a bunch of PITA nuances in the various programs. Sometimes the spreasheets programs (Appleworks, Excel, Numbers, Calc (open office)...etc) will evaluate a Blank as a 0 and not NULL/Blank depeding on the version, formatting, phase of the moon. Each one is has its own nuances of frustreration 

BReligion


----------



## tilt (Mar 3, 2005)

Thanks gents! I shall fiddle around with the SUMIF function and see if I can make that work, 

Cheers

*Edited to add: Sorry folks, I could never get SUMIF or SUMIFS to work, I get getting Syntax and other errors in the formula irrespective of the changes I made, so I gave up and created another column to manually re-enter the durations of the listened podcasts and totalled the values up top. Completely old-school I am afraid  Thanks again for your suggestions.


----------



## polywog (Aug 9, 2007)

tilt said:


> Hi,
> 
> I need conditional formula help in Numbers. I am completely lost and would appreciate help here please. I am listening to PMI podcasts of different durations to earn PDUs. I am tracking the title and duration of each podcast. I need to enter the date I listened to each podcast, and add up all the durations I have listed to in one cell.
> 
> ...


BReligion is correct:

You need an extra column with the formula =IF(ISBLANK(E1),"0m",D1)

Then your sum cell is the sum of Column F.

The error is likely because if you use duration as a format, the cells must contain a duration if you sum them.


----------

