| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Follow up Question to Countif
Hi All
Secondary question could I adapt the formula =SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting inspection")) to give a count using the criteria of between two dates. Eg Count Awaiting Inspection entries if date in column B is between 1st feb 2009 and 15th Feb 2009. Many thanx Ajay |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Follow up Question to Countif
to give a count using the criteria of between two dates.
Usually, when someone says "between dates" what they really mean is all dates between, *including* the boundary dates. Use cells to hold the boundary dates and your other criteria: A1 = lower boundary date = 2/1/2009 A2 = upper boundary date = 2/15/2009 A3 = Awaiting inspection Then: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=B1),--(I1:I36=A3)) -- Biff Microsoft Excel MVP "Ajay" wrote in message ... Hi All Secondary question could I adapt the formula =SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting inspection")) to give a count using the criteria of between two dates. Eg Count Awaiting Inspection entries if date in column B is between 1st feb 2009 and 15th Feb 2009. Many thanx Ajay |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Follow up Question to Countif
Ooops!
Typo: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=B1),--(I1:I36=A3)) Should be: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=A2),--(I1:I36=A3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... to give a count using the criteria of between two dates. Usually, when someone says "between dates" what they really mean is all dates between, *including* the boundary dates. Use cells to hold the boundary dates and your other criteria: A1 = lower boundary date = 2/1/2009 A2 = upper boundary date = 2/15/2009 A3 = Awaiting inspection Then: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=B1),--(I1:I36=A3)) -- Biff Microsoft Excel MVP "Ajay" wrote in message ... Hi All Secondary question could I adapt the formula =SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting inspection")) to give a count using the criteria of between two dates. Eg Count Awaiting Inspection entries if date in column B is between 1st feb 2009 and 15th Feb 2009. Many thanx Ajay |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Follow up Question to Countif
Excellent many thanks just what i needed
"T. Valko" wrote: Ooops! Typo: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=B1),--(I1:I36=A3)) Should be: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=A2),--(I1:I36=A3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... to give a count using the criteria of between two dates. Usually, when someone says "between dates" what they really mean is all dates between, *including* the boundary dates. Use cells to hold the boundary dates and your other criteria: A1 = lower boundary date = 2/1/2009 A2 = upper boundary date = 2/15/2009 A3 = Awaiting inspection Then: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=B1),--(I1:I36=A3)) -- Biff Microsoft Excel MVP "Ajay" wrote in message ... Hi All Secondary question could I adapt the formula =SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting inspection")) to give a count using the criteria of between two dates. Eg Count Awaiting Inspection entries if date in column B is between 1st feb 2009 and 15th Feb 2009. Many thanx Ajay |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Follow up Question to Countif
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Ajay" wrote in message ... Excellent many thanks just what i needed "T. Valko" wrote: Ooops! Typo: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=B1),--(I1:I36=A3)) Should be: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=A2),--(I1:I36=A3)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... to give a count using the criteria of between two dates. Usually, when someone says "between dates" what they really mean is all dates between, *including* the boundary dates. Use cells to hold the boundary dates and your other criteria: A1 = lower boundary date = 2/1/2009 A2 = upper boundary date = 2/15/2009 A3 = Awaiting inspection Then: =SUMPRODUCT(--(C1:C36=A1),--(C1:C36<=B1),--(I1:I36=A3)) -- Biff Microsoft Excel MVP "Ajay" wrote in message ... Hi All Secondary question could I adapt the formula =SUMPRODUCT((C1:C36<DATE(2009,1,31))*(I1:I36="Awai ting inspection")) to give a count using the criteria of between two dates. Eg Count Awaiting Inspection entries if date in column B is between 1st feb 2009 and 15th Feb 2009. Many thanx Ajay |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A follow up Question | Excel Discussion (Misc queries) | |||
| Follow-up AVERAGEIF question | New Users to Excel | |||
| follow-up question regarding secondary Y-axes | Charts and Charting in Excel | |||
| Follow-Up (Clarification) to MIN question | Excel Discussion (Misc queries) | |||
| Follow on from simple question 12/8/2004 | Excel Discussion (Misc queries) | |||
Linear Mode
