| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF With Variable Consecutive Days
Okay, I'll try that. Thanks for your help.
Peanut "Duke Carey" wrote: Since you can control the column headers, doesn't that make the weekends and holdiays a moot point? Meaning, there shouldn't be any weekend or holiday dates in the column headers, right? Let's say that customer ABC's data goes across row 2, in columns B:Z (that gives you 25 days/month, more than enough to cover a month of workdays). In cell AA2, enter this formula and copy it across as many columns as needed =IF(COUNTIF(B2:D2,"0")=3,SUM(B2:D2),0) Then take the MAX(AA2:AY2) Repeat for each customer. "Peanut" wrote: Sorry about the confusion. I have the data in the following format: May 2007 Customer 5/1 5/2 5/3 ... 5/6 ABC 33 21 15 5 XYZ 17 14 9 0 JKL 25 11 6 13 Your first idea would work - except I have a large number of customers. I hope this helps. Peanut "Duke Carey" wrote: Your description is confusing. The days are in columns - does that mean you have the days going across the top of the sheet from left to right? Then why do you have one day per line per cusomter? Why wouldn't you have all of a single cusotmer's activity across a single line with entries in the columns for the days when they had activity? It'd be far better if you had: Customer Date Activity ABC 5/1/07 33 ABC 5/2/07 17 ABC 5/6/07 34 At least that you could put into a database and, with a little work, spit out the answer you want. If you have Customer May1 May2 ... May6 ABC 33 17 34 well, that's messy and quite a bit harder, but it might be a starting point But, if you have Customer May1 May2 ... May6 ABC 33 ABC 17 ABC 34 well, that's a real mess. So, what do you have "Peanut" wrote: All of my customers are in one sheet - I have about 40 customers. It is listed with the customer names and accounts in rows and the days in columns with one day per line per customer. And yes, I need to ignore holidays as well. I do have Access, but I'm not well schooled in how it works. Perhaps the only way to do this is to make 30 different formulas - each adding 3 days, then yet another formula to take the largest of those sums. But then again, I don't know how to separate the weekdays from the weekends and holidays - unless I add a non-value in those days... "Duke Carey" wrote: Even though your data is in a spreadsheet, Excel is probably NOT the best vehicle for this. Do you have all your customers in the same spreadsheet, or just one per sheet? You said you need to ignore weekends. What about holidays? Is there one line per day per customer, or do you need to aggregate data into a single days' activity? Finally, do you MS Access or another database? "Peanut" wrote: I have a spreadsheet that records the daily activity of a customer's account. Due to various limits on how much each customer is allowed to utilize their accounts, I have been charged to record, within a certain time frame (the most recent month), the sum of 3 consecutive days of activity. However, I need to report the sum of the 3 consecutive days with the largest level of activity to gauge if the customers "toe the line" at any time. And for another wrench in the formula - I need to ignore weekends, but still recognize weekdays with no activity. Help! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I change that excel traces only consecutive days in a line cha | Charts and Charting in Excel | |||
| Assign a date to a record for consecutive days | Excel Worksheet Functions | |||
| Consecutive Days (Not NETWORKDAYS) | Excel Discussion (Misc queries) | |||
| Listing consecutive days without inputting each one individually | Charts and Charting in Excel | |||
| sumif/countif on non-consecutive colums - array? | Excel Worksheet Functions | |||
Threaded Mode
