Help with SUMIF and criteria
 

I'm building a sheet that lists product units over a twelve month
period. I want to calculate the SUM of the first three months where
there's been activity...find the first cell within the twelve month
range that has a positive units sold value and sum that cell with the
next two adjacent cells, which can be either positive or negative.
(The initial cell must be positive.)

Any tips on how to build this a formula for this are greatly
appreciated!

Help with SUMIF and criteria
 

Hi John,
Some assumptions: Your products are listes in column A and your month
are listed in row 1. So the activitie for the first product in the
first month would be listed in cell B2. then try the following formula
for the first product in row 2:
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank



John wrote:
I'm building a sheet that lists product units over a twelve month
period. I want to calculate the SUM of the first three months where
there's been activity...find the first cell within the twelve month
range that has a positive units sold value and sum that cell with the
next two adjacent cells, which can be either positive or negative.
(The initial cell must be positive.)

Any tips on how to build this a formula for this are greatly
appreciated!

Help with SUMIF and criteria
 

Frank,
I should have mentioned, the list is rows structured like this:
product, month1 units, month2 units, month3 units...month12 units,
initial 3mo units.

I need to help with a formula to calc the "initial 3mo units." (I'm
actually simplifying the data list for the purpose of discussion.)


"Frank Kabel" wrote in message ...
Hi John,
Some assumptions: Your products are listes in column A and your month
are listed in row 1. So the activitie for the first product in the
first month would be listed in cell B2. then try the following formula
for the first product in row 2:
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank

Help with SUMIF and criteria
 

Hi John

if I understood you correctly this seems to be the same as my
assumption. E.g.
A2: Product
B2: month 1 units
C2: month 2 units
....
M2: month 12 units

If this is your spreadsheet layout, you can use my formula. If you have
a differing layout you can 8if you like) send me your spreadsheet and
I'll adapt the formula for you

HTH
Frank


John wrote:
Frank,
I should have mentioned, the list is rows structured like this:
product, month1 units, month2 units, month3 units...month12 units,
initial 3mo units.

I need to help with a formula to calc the "initial 3mo units." (I'm
actually simplifying the data list for the purpose of discussion.)


"Frank Kabel" wrote in message
...
Hi John,
Some assumptions: Your products are listes in column A and your
month
are listed in row 1. So the activitie for the first product in the
first month would be listed in cell B2. then try the following
formula for the first product in row 2:
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank

Help with SUMIF and criteria
 

Frank, thanks a bunch for your help! Your suggestion seems to work just fine.
-John


=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

Help with SUMIF and criteria
 

Sorry, but I thought I'd be able to figure out the "real" formula
after getting something to work with from here. Frank, do you or
anyone else have a suggestion as to how to modify your formula to get
work with this exact data:

Product, StreetDate, Month1, Month2, Month3..Month12,
Initial_3month_Activity

Where Initial_3Mo_Activity sums the three contiguous month values
starting at the Month# corresponding to its StreetDate (instead of the
first positive Month value).

Any tips are greatly appreciated! (I may need to repost this as a new
thread if there aren't any responses since this has nothing to do with
SUMIF.)

(Frank's formula:)
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))

-j



"Frank Kabel" wrote in message ...
Hi John,
Some assumptions: Your products are listes in column A and your month
are listed in row 1. So the activitie for the first product in the
first month would be listed in cell B2. then try the following formula
for the first product in row 2:
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank



John wrote:
I'm building a sheet that lists product units over a twelve month
period. I want to calculate the SUM of the first three months where
there's been activity...find the first cell within the twelve month
range that has a positive units sold value and sum that cell with the
next two adjacent cells, which can be either positive or negative.
(The initial cell must be positive.)

Any tips on how to build this a formula for this are greatly
appreciated!

Help with SUMIF and criteria
 

=SUM(OFFSET(C2,0,MATCH(TRUE,C2:N20,0)-1,1,3))

--
Regards,
Tom Ogilvy

John wrote in message
om...
Sorry, but I thought I'd be able to figure out the "real" formula
after getting something to work with from here. Frank, do you or
anyone else have a suggestion as to how to modify your formula to get
work with this exact data:

Product, StreetDate, Month1, Month2, Month3..Month12,
Initial_3month_Activity

Where Initial_3Mo_Activity sums the three contiguous month values
starting at the Month# corresponding to its StreetDate (instead of the
first positive Month value).

Any tips are greatly appreciated! (I may need to repost this as a new
thread if there aren't any responses since this has nothing to do with
SUMIF.)

(Frank's formula:)
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))

-j



"Frank Kabel" wrote in message
...
Hi John,
Some assumptions: Your products are listes in column A and your month
are listed in row 1. So the activitie for the first product in the
first month would be listed in cell B2. then try the following formula
for the first product in row 2:
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank



John wrote:
I'm building a sheet that lists product units over a twelve month
period. I want to calculate the SUM of the first three months where
there's been activity...find the first cell within the twelve month
range that has a positive units sold value and sum that cell with the
next two adjacent cells, which can be either positive or negative.
(The initial cell must be positive.)

Any tips on how to build this a formula for this are greatly
appreciated!

Help with SUMIF and criteria
 

After rereading your problem statement, assuming B2 will contain a value
between 1 and 12 inclusive:

=SUM(OFFSET(B2,0,B2,1,3))



--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
=SUM(OFFSET(C2,0,MATCH(TRUE,C2:N20,0)-1,1,3))

--
Regards,
Tom Ogilvy

John wrote in message
om...
Sorry, but I thought I'd be able to figure out the "real" formula
after getting something to work with from here. Frank, do you or
anyone else have a suggestion as to how to modify your formula to get
work with this exact data:

Product, StreetDate, Month1, Month2, Month3..Month12,
Initial_3month_Activity

Where Initial_3Mo_Activity sums the three contiguous month values
starting at the Month# corresponding to its StreetDate (instead of the
first positive Month value).

Any tips are greatly appreciated! (I may need to repost this as a new
thread if there aren't any responses since this has nothing to do with
SUMIF.)

(Frank's formula:)
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))

-j



"Frank Kabel" wrote in message
...
Hi John,
Some assumptions: Your products are listes in column A and your month
are listed in row 1. So the activitie for the first product in the
first month would be listed in cell B2. then try the following formula
for the first product in row 2:
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank



John wrote:
I'm building a sheet that lists product units over a twelve month
period. I want to calculate the SUM of the first three months where
there's been activity...find the first cell within the twelve month
range that has a positive units sold value and sum that cell with
the
next two adjacent cells, which can be either positive or negative.
(The initial cell must be positive.)

Any tips on how to build this a formula for this are greatly
appreciated!

Help with SUMIF and criteria
 

Actually, B2 should never contain a value greater than 10 or you will go
beyond your data.

--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
After rereading your problem statement, assuming B2 will contain a value
between 1 and 12 inclusive:

=SUM(OFFSET(B2,0,B2,1,3))



--
Regards,
Tom Ogilvy

Tom Ogilvy wrote in message
...
=SUM(OFFSET(C2,0,MATCH(TRUE,C2:N20,0)-1,1,3))

--
Regards,
Tom Ogilvy

John wrote in message
om...
Sorry, but I thought I'd be able to figure out the "real" formula
after getting something to work with from here. Frank, do you or
anyone else have a suggestion as to how to modify your formula to get
work with this exact data:

Product, StreetDate, Month1, Month2, Month3..Month12,
Initial_3month_Activity

Where Initial_3Mo_Activity sums the three contiguous month values
starting at the Month# corresponding to its StreetDate (instead of the
first positive Month value).

Any tips are greatly appreciated! (I may need to repost this as a new
thread if there aren't any responses since this has nothing to do with
SUMIF.)

(Frank's formula:)
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))

-j



"Frank Kabel" wrote in message
...
Hi John,
Some assumptions: Your products are listes in column A and your
month
are listed in row 1. So the activitie for the first product in the
first month would be listed in cell B2. then try the following
formula
for the first product in row 2:
=SUM(OFFSET(B2,0,MATCH(TRUE,B2:M20,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank



John wrote:
I'm building a sheet that lists product units over a twelve month
period. I want to calculate the SUM of the first three months
where
there's been activity...find the first cell within the twelve
month
range that has a positive units sold value and sum that cell with
the
next two adjacent cells, which can be either positive or negative.
(The initial cell must be positive.)

Any tips on how to build this a formula for this are greatly
appreciated!