Countif using multiple references

  • Thread starter Thread starter K
  • Start date Start date
K

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?
 
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
 
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.
 
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<>""))
 
=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

--
 
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.
 
Back
Top