Help with SUMIF and criteria

  • Thread starter Thread starter John
  • Start date Start date
J

John

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!
 
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:M2>0,0)-1,1,3))
enter this as array formula (CTRL+SHIFT+ENTER)

HTH
Frank
 
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.)
 
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
 
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:M2>0,0)-1,1,3))

-j
 
Back
Top