Countif using multiple references

[K]

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?

 

[PCLIVE]

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

 

[T. Valko]

I want to count the data in range c-h

What *exactly* do you want to count?

 

[K]

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]

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<>""))

 

[PCLIVE]

=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]

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.