SUMIF With Variable Consecutive Days
 

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!

SUMIF With Variable Consecutive Days
 

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!

SUMIF With Variable Consecutive Days
 

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!

SUMIF With Variable Consecutive Days
 

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!

SUMIF With Variable Consecutive Days
 

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!

SUMIF With Variable Consecutive Days
 

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!

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!