Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing Multiple Cell References to Multiple Values (NOT total | Excel Discussion (Misc queries) | |||
Transposing Multiple Cell references as Multiple Values | Excel Discussion (Misc queries) | |||
CountIf does not work with an Absolute references | Excel Discussion (Misc queries) | |||
wildcard in countif formula that uses cell references | Excel Discussion (Misc queries) | |||
Cell references in a countif function? | Excel Discussion (Misc queries) |