Prev: Desire Weekday returned in every 5th Cell
Next: How do I format cells in Excel to make 175/5 read 175?
From: Curtis on 22 Feb 2010 12:10
Thanks...This works however my source data cols F, G, H (in x) containing
> the day, month, year numbers may not always have data in th respective range for all divisions. Anyway around this

Thanks

"Max" wrote:

> If A5 down contains real dates (eg 12 Nov 2009) -- the date format applied is
> immaterial -- then this simpler rendition in D5 should work fine:
> =SUMPRODUCT((x!$E$2:$E$10=D$4)*(DATE(x!$H$2:$H$10,x!$G$2:$G$10,x!$F$2:$F$10)=$A5)*(x!$N$2:$N$10=TRUE),x!$I$2:$I$10)
> Copy D5 across to S5, fill down to populate
>
> As mentioned in my 1st response, the source cols F, G, H (in x) containing
> the day, month, year numbers should be fully populated throughout the range,
> otherwise the formula will return #NUM! Success? celebrate it, hit YES below
> --
> Max
> Singapore
> ---
> "Curtis" wrote:
> > Thanks Max
> >
> > is it possible to change the formula if my results worksheet has the date
> > consolidated. SO rather than Column A= yr, B = mth, c= day, Column a would be
> > formated as 1-Jan=10, 2-Jan-10, 3-Jan-10, etc...
> >
> > Thanks
> >
> > In a different worksheet called results (same workbook)
> > >
> > > Column A represents the Year ( 2009/ 2010)
> > > Column B represents the month of the year (format Jan thru Dec)
> > > Column C represents the day of the month (ex: 1 through 31)
> > >
> > > Row 4, columns D thru S represent the divisions
>
From: Max on 22 Feb 2010 18:20
As-is, no. You need to check/touch up the source data (in x) for completeness
(eg via autofilter).
--
Max
Singapore
---
"Curtis" wrote:
> Thanks...This works however my source data cols F, G, H (in x) containing
> > the day, month, year numbers may not always have data in th respective range for all divisions. Anyway around this

First  |  Prev  | 
Pages: 1 2
Prev: Desire Weekday returned in every 5th Cell
Next: How do I format cells in Excel to make 175/5 read 175?