Help with =IF(AND formula in excel 2

[alan1203]

I have a spreadsheet with outstanding repair / maintenance jobs. I have been asked to break that down into jobs that are > than 3 months old and then break them down into Yearly stats.

I think i have the greater than 3 months jobs sorted with

=IF(AND(PlannedDate <> "",PlannedDate < CutOffDate,Location <> "ITU",Location <>"NETS",JobType = "PPM"),1,0)

Which takes out the jobs with no planned date and a planned date less than the cut off date and doesn't contain either of the locations and has a job type as "PPM". Then using the SUM formula in another 'Totals' sheet.

My problem is with trying to break it down into yearly stats.

I've used

=IF(AND(PlannedDate<CutOffDate,PlannedDate<>"",Location<>"ITU",Location<>"NETS",PlannedDate>=1/1/2012,PlannedDate<=31/12/2012),1,0)

The SUM for this returns 0 even though I know the answer should be more than that.

Any help would be appreciated.

Thanks
Alan

 

[SkipVought]

hi,

assuming that you're working with tabular data, and I'd recommend using the Structured Table feature in Excel 2007+, this can be very easily accomplished using either the SUMIFS() function or the SUMPRODUCT() function, the latter of which I prefer.

Lay out your data with DATEs across columns, lt's say in row 1, starting in column D, might look something like this, using Named Ranges, as it seems you ARE using ...

=sumproduct((PlannedDate<CutOffDate)*(or(Location<>"ITU",Location<>"NETS"))*(PlannedDate>=D$1)*(PlannedDate<E$1))

where D1 contains your first month-begin data and E1 the second and so on. Note that you must have one more date than the last month you are reporting.

Columns A-C contain your PlannedDate, CutOffDate and Location

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

This might work better...
[tt]
=sumproduct((PlannedDate<CutOffDate)*(Location<>"ITU")*(Location<>"NETS")*(PlannedDate>=D$1)*(PlannedDate<E$1))
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

In your formula 1/1/2012 = 0.000497018. Use DATE(2012,1,1) instead.

combo

 

[alan1203]

Thanks Skip that works a treat. What a good function that is.
Thanks also to Combo for your reply. I was thinking that there was something wrong with the way I was referencing dates. I'll keep that in mind for the future.

 

[SkipVought]

FYI,

Although you can enter literals like dates into a formula, it is generally a better practice to use references in a formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[alan1203]

Thanks Skip. How would I use a reference for a date?

 

[alan1203]

Thanks Skip. How would I use a reference for a date?

 

[alan1203]

Thanks Skip. How would I use a reference for a date?

 

[SkipVought]

If what you are doing is anythin like things I've done in the past, the dates are either in a column to the left or right of the cell containing the formula or in a row, usually above the cell containing the formula. If that formula is to be copied to multiple cells, make sure that the proper absolute and relative notations are included: ($A5 for instance for copying a formula across and Z$1 for instance for copying a formula down.)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

For instance, suppose that your dates were like this, in column A

PlndDT    
[highlight][red]1/1/2011[/red][/highlight]   [b]=sumproduct((PlannedDate<CutOffDate)*(Location<>"ITU")*(Location<>"NETS")*(PlannedDate>=[highlight][red]$A2[/red][/highlight])*(PlannedDate<[highlight][green]$A3[/green][/highlight])) [/b]
[highlight][green]1/1/2012[/green][/highlight]
1/1/2013

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[zelgar]

I know I'm late to this thread, but have you considered using the MONTH and YEAR date functions in Excel.