| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT to find occurences within date range
I have the following formula:
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
Hi,
I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
Hi Mike
This one has me puzzled - try as I may, I keep getting a difference between my manual calculations and the results of the formula below. Any advice? "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
P.S. I forgot to add: when compared to manual calculations, the formula is
wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
Hi,
I still think the formula is fine and you have a data issue. In a spare column enter =isnumber(a3) drag down and all should evaluate as TRUE and if they don't, your dates aren't all dates and may be text. Mike "WildWill" wrote: P.S. I forgot to add: when compared to manual calculations, the formula is wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
All came back "True" - can I not send you the database? (sorry if I am being
too forward - but this is an amazing situation to me) "Mike H" wrote: Hi, I still think the formula is fine and you have a data issue. In a spare column enter =isnumber(a3) drag down and all should evaluate as TRUE and if they don't, your dates aren't all dates and may be text. Mike "WildWill" wrote: P.S. I forgot to add: when compared to manual calculations, the formula is wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
You can upload it here and post the link
http://www.savefile.com/ Mike "WildWill" wrote: All came back "True" - can I not send you the database? (sorry if I am being too forward - but this is an amazing situation to me) "Mike H" wrote: Hi, I still think the formula is fine and you have a data issue. In a spare column enter =isnumber(a3) drag down and all should evaluate as TRUE and if they don't, your dates aren't all dates and may be text. Mike "WildWill" wrote: P.S. I forgot to add: when compared to manual calculations, the formula is wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
http://www.savefile.com/files/2082188
On the Analysis tab, I have highlighted the cells that have the formula. "Mike H" wrote: You can upload it here and post the link http://www.savefile.com/ Mike "WildWill" wrote: All came back "True" - can I not send you the database? (sorry if I am being too forward - but this is an amazing situation to me) "Mike H" wrote: Hi, I still think the formula is fine and you have a data issue. In a spare column enter =isnumber(a3) drag down and all should evaluate as TRUE and if they don't, your dates aren't all dates and may be text. Mike "WildWill" wrote: P.S. I forgot to add: when compared to manual calculations, the formula is wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
Hi,
Dates were being counted twice. Excuse the scrawl all over it I put it there to help you understand the changes i made http://www.savefile.com/files/2082282 Mike "WildWill" wrote: http://www.savefile.com/files/2082188 On the Analysis tab, I have highlighted the cells that have the formula. "Mike H" wrote: You can upload it here and post the link http://www.savefile.com/ Mike "WildWill" wrote: All came back "True" - can I not send you the database? (sorry if I am being too forward - but this is an amazing situation to me) "Mike H" wrote: Hi, I still think the formula is fine and you have a data issue. In a spare column enter =isnumber(a3) drag down and all should evaluate as TRUE and if they don't, your dates aren't all dates and may be text. Mike "WildWill" wrote: P.S. I forgot to add: when compared to manual calculations, the formula is wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
Mike, AWESOME! Thanks a lot.
"Mike H" wrote: Hi, Dates were being counted twice. Excuse the scrawl all over it I put it there to help you understand the changes i made http://www.savefile.com/files/2082282 Mike "WildWill" wrote: http://www.savefile.com/files/2082188 On the Analysis tab, I have highlighted the cells that have the formula. "Mike H" wrote: You can upload it here and post the link http://www.savefile.com/ Mike "WildWill" wrote: All came back "True" - can I not send you the database? (sorry if I am being too forward - but this is an amazing situation to me) "Mike H" wrote: Hi, I still think the formula is fine and you have a data issue. In a spare column enter =isnumber(a3) drag down and all should evaluate as TRUE and if they don't, your dates aren't all dates and may be text. Mike "WildWill" wrote: P.S. I forgot to add: when compared to manual calculations, the formula is wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
Glad I could help and thanks for the feedback
"WildWill" wrote: Mike, AWESOME! Thanks a lot. "Mike H" wrote: Hi, Dates were being counted twice. Excuse the scrawl all over it I put it there to help you understand the changes i made http://www.savefile.com/files/2082282 Mike "WildWill" wrote: http://www.savefile.com/files/2082188 On the Analysis tab, I have highlighted the cells that have the formula. "Mike H" wrote: You can upload it here and post the link http://www.savefile.com/ Mike "WildWill" wrote: All came back "True" - can I not send you the database? (sorry if I am being too forward - but this is an amazing situation to me) "Mike H" wrote: Hi, I still think the formula is fine and you have a data issue. In a spare column enter =isnumber(a3) drag down and all should evaluate as TRUE and if they don't, your dates aren't all dates and may be text. Mike "WildWill" wrote: P.S. I forgot to add: when compared to manual calculations, the formula is wrong. "Mike H" wrote: Hi, I can't see anything wrong with the formula so if your getting unexpected results then maybe the dates are suspect, are they really dates or text that looks like a date. Mike "WildWill" wrote: I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
SUMPRODUCT to find occurences within date range
=SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3 :$A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=D ATE(2009,4,3))))
You have a misplaced parenthesis. Try it like this: =SUMPRODUCT(--(Input!$C$3:$C$48943<""),--(Input!$A$3:$A$48943=DATE(2009,3,30)),--(Input!$A$3:$A$48943<=DATE(2009,4,3))) Better to use cells to hold the date criteria: A1 = 3/30/2009 B1 = 4/3/2009 =SUMPRODUCT(--(Input!$C$3:$C$48943<""),--(Input!$A$3:$A$48943=A1),--(Input!$A$3:$A$48943<=B1)) -- Biff Microsoft Excel MVP "WildWill" wrote in message ... I have the following formula: =SUMPRODUCT((Input!$C$3:$C$48943<"")*(Input!$A$3: $A$48943=DATE(2009,3,30)*(Input!$A$3:$A$48943<=DA TE(2009,4,3)))) I am using this formula in a rather large spreadsheet, A3:J48943, which is a database logging daily activities of a Marketing Team. Column A contains date values of the specific activity and Column C contains text values indicating the activity type, i.e. Cold Calling, Client Visit, etc, etc. I am trying to achieve the following: Counting the total number of activities per week. Is my formula above correct? Somehow I doubt it because when I run a COUNTA on Column C, I get a different value to the totals reported per week using the above formula. Please help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMPRODUCT to calculate unique occurences of string in column of d | Excel Discussion (Misc queries) | |||
| SUMPRODUCT: Help to use this to find min date in range for criter | Excel Discussion (Misc queries) | |||
| Sumproduct with date range | Excel Worksheet Functions | |||
| Sumproduct Will Not Find Date | Excel Worksheet Functions | |||
| How do I count occurences in a date range? | Excel Worksheet Functions | |||
Linear Mode
