Countif using multiple references
 

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?

Countif using multiple references
 

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" wrote in message
...
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?

Countif using multiple references
 

I want to count the data in range c-h

What *exactly* do you want to count?

--
Biff
Microsoft Excel MVP


"K" wrote in message
...
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?

Countif using multiple references
 

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" wrote in message
...
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?

Countif using multiple references
 

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

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

Or, use cells to hold your date range:

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

=SUMPRODUCT((A1:A10=F1)*(B1:B10<=G1)*(C1:D10<"") )

--
Biff
Microsoft Excel MVP


"K" wrote in message
...
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" wrote in message
...
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?

Countif using multiple references
 

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

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

HTH,
Paul

--

"K" wrote in message
...
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" wrote in message
...
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?

Countif using multiple references
 

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")+(D2:D100="x"))

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

HTH,
Paul

--

"K" wrote in message
...
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" wrote in message
...
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?