| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating number of occurences based on dates
I need to calculate how many times that a new item have been put in excel
based on the last months entries. Anyone got a way to do that ? (It needs to be a running proces, so that we at all times can see the last month (or 3 months or 12 months). When this then functions I need to sum the quotations values. Best regards Henrik Fritsche |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating number of occurences based on dates
More info required, what is your data, how do you determine a new item, etc.
etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrik Fritsche" wrote in message ... I need to calculate how many times that a new item have been put in excel based on the last months entries. Anyone got a way to do that ? (It needs to be a running proces, so that we at all times can see the last month (or 3 months or 12 months). When this then functions I need to sum the quotations values. Best regards Henrik Fritsche |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating number of occurences based on dates
The system is for keeping track of how many quotations we make and which
values the quotes have. I list various information for each quote and have - date received - value - date quoted - date for follow up. What I need to be able to is to know - how many quotes are made within the last month, 3 months or 12 months - which value did the quotes have Offcourse I also have other parameters like - did it turn out as order or no order and why - which sales responsible have which quotes - which sales internal have which quotes and so forth. Hope you can help out, as I am a little stuck. Henrik -- Henrik Fritsche "Bob Phillips" skrev: More info required, what is your data, how do you determine a new item, etc. etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrik Fritsche" wrote in message ... I need to calculate how many times that a new item have been put in excel based on the last months entries. Anyone got a way to do that ? (It needs to be a running proces, so that we at all times can see the last month (or 3 months or 12 months). When this then functions I need to sum the quotations values. Best regards Henrik Fritsche |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating number of occurences based on dates
This should get you started
=SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY()-2),--(MONTH(C2:C200)<=MONTH(TOD AY())) =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY()-2),--(MONTH(C2:C200)<=MONTH(TOD AY())) quotes in last 3 months =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY()-2),--(MONTH(C2:C200)<=MONTH(TOD AY()),B2:B200) for the value assumes quote date in column C, value in column B. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrik Fritsche" wrote in message ... The system is for keeping track of how many quotations we make and which values the quotes have. I list various information for each quote and have - date received - value - date quoted - date for follow up. What I need to be able to is to know - how many quotes are made within the last month, 3 months or 12 months - which value did the quotes have Offcourse I also have other parameters like - did it turn out as order or no order and why - which sales responsible have which quotes - which sales internal have which quotes and so forth. Hope you can help out, as I am a little stuck. Henrik -- Henrik Fritsche "Bob Phillips" skrev: More info required, what is your data, how do you determine a new item, etc. etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrik Fritsche" wrote in message ... I need to calculate how many times that a new item have been put in excel based on the last months entries. Anyone got a way to do that ? (It needs to be a running proces, so that we at all times can see the last month (or 3 months or 12 months). When this then functions I need to sum the quotations values. Best regards Henrik Fritsche |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating number of occurences based on dates
Sorry, it seems not to function.
Isnt SUMPRODUCT a times by times calculation ? Have you got such values functioning in your end by using it ? Best regards Henrik -- Henrik Fritsche "Bob Phillips" skrev: This should get you started =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY()-2),--(MONTH(C2:C200)<=MONTH(TOD AY())) =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY()-2),--(MONTH(C2:C200)<=MONTH(TOD AY())) quotes in last 3 months =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY()-2),--(MONTH(C2:C200)<=MONTH(TOD AY()),B2:B200) for the value assumes quote date in column C, value in column B. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrik Fritsche" wrote in message ... The system is for keeping track of how many quotations we make and which values the quotes have. I list various information for each quote and have - date received - value - date quoted - date for follow up. What I need to be able to is to know - how many quotes are made within the last month, 3 months or 12 months - which value did the quotes have Offcourse I also have other parameters like - did it turn out as order or no order and why - which sales responsible have which quotes - which sales internal have which quotes and so forth. Hope you can help out, as I am a little stuck. Henrik -- Henrik Fritsche "Bob Phillips" skrev: More info required, what is your data, how do you determine a new item, etc. etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Henrik Fritsche" wrote in message ... I need to calculate how many times that a new item have been put in excel based on the last months entries. Anyone got a way to do that ? (It needs to be a running proces, so that we at all times can see the last month (or 3 months or 12 months). When this then functions I need to sum the quotations values. Best regards Henrik Fritsche |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculating number of occurences based on dates
"Henrik Fritsche" wrote in message ... Sorry, it seems not to function. Isnt SUMPRODUCT a times by times calculation ? Hoistorically yes, but see http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed discussion on it's evolvement. Have you got such values functioning in your end by using it ? Had a small type, try these 3 =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY())-2), --(MONTH(C2:C200)<=MONTH(TODAY()))) =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY())-2), --(MONTH(C2:C200)<=MONTH(TODAY()))) =SUMPRODUCT(--(MONTH(C2:C200)=MONTH(TODAY())-2), --(MONTH(C2:C200)<=MONTH(TODAY())),B2:B200) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count occurences based on dates | Excel Worksheet Functions | |||
| formula for calculating repeat occurences between specified dates | Excel Discussion (Misc queries) | |||
| Calculating cells based on dates | Excel Worksheet Functions | |||
| Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
| Calculating Due Dates Based on Payments | Excel Worksheet Functions | |||
Hybrid Mode
