hey guys please help me out.

I have a flat file that has start date in column a, and end date in column b
and data in columns c - h. I want to count the data in range c-h but only if
the start and end dates in column a and b are within a certain date parameter.

I have read differnt answers here using countif and also sumproduct but I'm
not sure what the argument should be or the syntax.

could you please help out?

With the start date you want to check agains in Z1 and the end date in Z2
(adjust as necessary):

=SUMPRODUCT(--(A2:A100>=Z1),--(B2:B100<=Z2))

Does that help?
Paul

--

"K" <(E-Mail Removed)> wrote in message
news:B8769EB2-AF85-484D-83EE-(E-Mail Removed)...
> hey guys please help me out.
>
> I have a flat file that has start date in column a, and end date in column
> b
> and data in columns c - h. I want to count the data in range c-h but only
> if
> the start and end dates in column a and b are within a certain date
> parameter.
>
> I have read differnt answers here using countif and also sumproduct but
> I'm
> not sure what the argument should be or the syntax.
>
> could you please help out?
>
>

>I want to count the data in range c-h

What *exactly* do you want to count?

--
Biff
Microsoft Excel MVP


"K" <(E-Mail Removed)> wrote in message
news:B8769EB2-AF85-484D-83EE-(E-Mail Removed)...
> hey guys please help me out.
>
> I have a flat file that has start date in column a, and end date in column
> b
> and data in columns c - h. I want to count the data in range c-h but only
> if
> the start and end dates in column a and b are within a certain date
> parameter.
>
> I have read differnt answers here using countif and also sumproduct but
> I'm
> not sure what the argument should be or the syntax.
>
> could you please help out?
>
>

Basically for I want to count all values in columns c-d only if the start and
end date for that in the corresponding row match my cirteria

example

column a column B column C Colum D

start date end date factory 1 Factory 2
6/1/08 6/30/08 x
x
7/1/08 7/2/08
x
6/1/08 6/2/08 x

So i want a total count columns c and d only if the start date >=6/1/08 and
<=6/30/08.

so the answer I want is 3.


"T. Valko" wrote:

> >I want to count the data in range c-h
>
> What *exactly* do you want to count?
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "K" <(E-Mail Removed)> wrote in message
> news:B8769EB2-AF85-484D-83EE-(E-Mail Removed)...
> > hey guys please help me out.
> >
> > I have a flat file that has start date in column a, and end date in column
> > b
> > and data in columns c - h. I want to count the data in range c-h but only
> > if
> > the start and end dates in column a and b are within a certain date
> > parameter.
> >
> > I have read differnt answers here using countif and also sumproduct but
> > I'm
> > not sure what the argument should be or the syntax.
> >
> > could you please help out?
> >
> >
>
>
>

Ok, basically, count C if they are not empty/blank...

=SUMPRODUCT((A1:A10>=DATE(2008,6,1))*(B1:B10<=DATE(2008,6,30))*(C110<>""))

Or, use cells to hold your date range:

F1 = 6/1/2008
G1 = 6/30/2008

=SUMPRODUCT((A1:A10>=F1)*(B1:B10<=G1)*(C110<>""))

--
Biff
Microsoft Excel MVP


"K" <(E-Mail Removed)> wrote in message
news:F1D8E61F-8580-4BB9-BF1D-(E-Mail Removed)...
> Basically for I want to count all values in columns c-d only if the start
> and
> end date for that in the corresponding row match my cirteria
>
> example
>
> column a column B column C Colum D
>
> start date end date factory 1 Factory
> 2
> 6/1/08 6/30/08 x
> x
> 7/1/08 7/2/08
> x
> 6/1/08 6/2/08 x
>
> So i want a total count columns c and d only if the start date >=6/1/08
> and
> <=6/30/08.
>
> so the answer I want is 3.
>
>
> "T. Valko" wrote:
>
>> >I want to count the data in range c-h
>>
>> What *exactly* do you want to count?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "K" <(E-Mail Removed)> wrote in message
>> news:B8769EB2-AF85-484D-83EE-(E-Mail Removed)...
>> > hey guys please help me out.
>> >
>> > I have a flat file that has start date in column a, and end date in
>> > column
>> > b
>> > and data in columns c - h. I want to count the data in range c-h but
>> > only
>> > if
>> > the start and end dates in column a and b are within a certain date
>> > parameter.
>> >
>> > I have read differnt answers here using countif and also sumproduct but
>> > I'm
>> > not sure what the argument should be or the syntax.
>> >
>> > could you please help out?
>> >
>> >
>>
>>
>>

=SUMPRODUCT(--(A2:A100>=Z1),--(B2:B100<=Z2),--(C2:C100="x")+(D2100="x"))

Again, Z1 is the start date you want to check against and Z2 is the end
date. Adjust as necessary.

HTH,
Paul

--

"K" <(E-Mail Removed)> wrote in message
news:F1D8E61F-8580-4BB9-BF1D-(E-Mail Removed)...
> Basically for I want to count all values in columns c-d only if the start
> and
> end date for that in the corresponding row match my cirteria
>
> example
>
> column a column B column C Colum D
>
> start date end date factory 1 Factory
> 2
> 6/1/08 6/30/08 x
> x
> 7/1/08 7/2/08
> x
> 6/1/08 6/2/08 x
>
> So i want a total count columns c and d only if the start date >=6/1/08
> and
> <=6/30/08.
>
> so the answer I want is 3.
>
>
> "T. Valko" wrote:
>
>> >I want to count the data in range c-h
>>
>> What *exactly* do you want to count?
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "K" <(E-Mail Removed)> wrote in message
>> news:B8769EB2-AF85-484D-83EE-(E-Mail Removed)...
>> > hey guys please help me out.
>> >
>> > I have a flat file that has start date in column a, and end date in
>> > column
>> > b
>> > and data in columns c - h. I want to count the data in range c-h but
>> > only
>> > if
>> > the start and end dates in column a and b are within a certain date
>> > parameter.
>> >
>> > I have read differnt answers here using countif and also sumproduct but
>> > I'm
>> > not sure what the argument should be or the syntax.
>> >
>> > could you please help out?
>> >
>> >
>>
>>
>>

Thanks guys this helps. Just one last question.

In my criteria, I want to include anything that begins with "RA" or ends
"O", so does the "RA*" or "*O" function work with sumproduct?

basically my criteria is start date, end date, if RA and if O. All these
are stored in seperate columns.

"PCLIVE" wrote:

> =SUMPRODUCT(--(A2:A100>=Z1),--(B2:B100<=Z2),--(C2:C100="x")+(D2100="x"))
>
> Again, Z1 is the start date you want to check against and Z2 is the end
> date. Adjust as necessary.
>
> HTH,
> Paul
>
> --
>
> "K" <(E-Mail Removed)> wrote in message
> news:F1D8E61F-8580-4BB9-BF1D-(E-Mail Removed)...
> > Basically for I want to count all values in columns c-d only if the start
> > and
> > end date for that in the corresponding row match my cirteria
> >
> > example
> >
> > column a column B column C Colum D
> >
> > start date end date factory 1 Factory
> > 2
> > 6/1/08 6/30/08 x
> > x
> > 7/1/08 7/2/08
> > x
> > 6/1/08 6/2/08 x
> >
> > So i want a total count columns c and d only if the start date >=6/1/08
> > and
> > <=6/30/08.
> >
> > so the answer I want is 3.
> >
> >
> > "T. Valko" wrote:
> >
> >> >I want to count the data in range c-h
> >>
> >> What *exactly* do you want to count?
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "K" <(E-Mail Removed)> wrote in message
> >> news:B8769EB2-AF85-484D-83EE-(E-Mail Removed)...
> >> > hey guys please help me out.
> >> >
> >> > I have a flat file that has start date in column a, and end date in
> >> > column
> >> > b
> >> > and data in columns c - h. I want to count the data in range c-h but
> >> > only
> >> > if
> >> > the start and end dates in column a and b are within a certain date
> >> > parameter.
> >> >
> >> > I have read differnt answers here using countif and also sumproduct but
> >> > I'm
> >> > not sure what the argument should be or the syntax.
> >> >
> >> > could you please help out?
> >> >
> >> >
> >>
> >>
> >>
>
>
>