| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Number of Items open longer than 1 week but less than 2 weeks
Hi All,
I have data that is setup with Owners in Column A and Date Purchased in Column B. What formula would I use to calculate the number of items that were purchased by Customer 1 between 1 and 2 weeks ago? THanks Andi |
|
#2
|
|||
|
|||
|
Hi Andi
One way =SUMPRODUCT(--($A$1:$A$100=customer_name),--($B$1:$B$100=DATE(2005,7,23)),--($B$1:$B$100<=DATE(2005,7,29)) Change ranges and date to suit. -- Regards Roger Govier "Andibevan" wrote in message ... Hi All, I have data that is setup with Owners in Column A and Date Purchased in Column B. What formula would I use to calculate the number of items that were purchased by Customer 1 between 1 and 2 weeks ago? THanks Andi |
|
#3
|
|||
|
|||
|
=SUMPRODUCT(--($A$2:$A$100="Customer
1"),--($B$2:$B$100TODAY()-14),--($B$2:$B$100<=TODAY()-7) -- HTH RP (remove nothere from the email address if mailing direct) "Andibevan" wrote in message ... Hi All, I have data that is setup with Owners in Column A and Date Purchased in Column B. What formula would I use to calculate the number of items that were purchased by Customer 1 between 1 and 2 weeks ago? THanks Andi |
|
#4
|
|||
|
|||
|
Great - Thanks Guys
"Bob Phillips" wrote in message ... =SUMPRODUCT(--($A$2:$A$100="Customer 1"),--($B$2:$B$100TODAY()-14),--($B$2:$B$100<=TODAY()-7) -- HTH RP (remove nothere from the email address if mailing direct) "Andibevan" wrote in message ... Hi All, I have data that is setup with Owners in Column A and Date Purchased in Column B. What formula would I use to calculate the number of items that were purchased by Customer 1 between 1 and 2 weeks ago? THanks Andi |
|
#5
|
|||
|
|||
|
How would I get this to work if Column B contains the date and time not just
the date - i.e. 6/7/2005 11:52:59 Hadn't spotted that when I originally posted. "Bob Phillips" wrote in message ... =SUMPRODUCT(--($A$2:$A$100="Customer 1"),--($B$2:$B$100TODAY()-14),--($B$2:$B$100<=TODAY()-7) -- HTH RP (remove nothere from the email address if mailing direct) "Andibevan" wrote in message ... Hi All, I have data that is setup with Owners in Column A and Date Purchased in Column B. What formula would I use to calculate the number of items that were purchased by Customer 1 between 1 and 2 weeks ago? THanks Andi |
|
#6
|
|||
|
|||
|
It still works, just starts at the beginning of the day.
-- HTH Bob Phillips "Andibevan" wrote in message ... How would I get this to work if Column B contains the date and time not just the date - i.e. 6/7/2005 11:52:59 Hadn't spotted that when I originally posted. "Bob Phillips" wrote in message ... =SUMPRODUCT(--($A$2:$A$100="Customer 1"),--($B$2:$B$100TODAY()-14),--($B$2:$B$100<=TODAY()-7) -- HTH RP (remove nothere from the email address if mailing direct) "Andibevan" wrote in message ... Hi All, I have data that is setup with Owners in Column A and Date Purchased in Column B. What formula would I use to calculate the number of items that were purchased by Customer 1 between 1 and 2 weeks ago? THanks Andi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to get week number in month in excel ? | Charts and Charting in Excel | |||
| Can excel automatically number itself each time you open a documen | Excel Discussion (Misc queries) | |||
| I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) | |||
| Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
| Returning the Week Number of a Specific Date on a Month | Excel Worksheet Functions | |||
Linear Mode
