Automatically increment a number in an Excel formula

[dchard]

I have the following formula that sums data from another worksheet for a particular product for a particular month. When I copy the formula from one month to the next I would like to automatically have the month incremented by 1 or be able to do a search and replace on the formula in a range

=SUMPRODUCT(('R:\group\FORECAST\FCAST 2004\[fcastpartsales.xls]Sheet1'!$C$1:$C$10000="SPA9000")*('R:\group\FORECAST\FCAST 2004\[fcastpartsales.xls]Sheet1'!$L$1:$L$10000)*(MONTH('R:\group\FORECAST\FCAST 2004\[fcastpartsales.xls]Sheet1'!$B$1:$B$10000)=1))

Thanks

deb

 

[mbarron]

You could do this a couple of ways - depending how your sheet is set up.

Scenario 1:
Each month is in it's own column. January in column 1, Feb in Column 2 etc.

...(MONTH('R:\group\FORECAST\FCAST 2004\[fcastpartsales.xls]Sheet1'!$B$1:$B$10000)=column()))


Scenario 2:
You can use a date formatted to show only the month as your header.

...)*(MONTH('R:\group\FORECAST\FCAST 2004\[fcastpartsales.xls]Sheet1'!$B$1:$B$10000)=month(month header cell)))


Mike

 

[dchard]

Mike

The spreadsheet that the formula is in has column headings Jan, Feb, Mar etc, the spreadsheet that the formula refers to has one column for the date of the sale and I am extracting the the data that is in column L based on the date in column B. So when I copy the formula from the Jan column to the Feb column I need to increment month from 1 to 2. I don't think the formula you mentioned will work.

Thanks

Deb

 

[mbarron]

I don't think the formula you mentioned will work[/qutoe]
Did you even try it? Unless you are thinking that I'm suggesting the formula snippet I posted was complete. I only altered the last arguement since the rest of the formula wouldn't change.


You don't give any information on how your sheet is configured.

For the first scenario:

The part of the formula I have highlighted will increment to the column number that the formula is entered into. So if January's info is to be displayed in Column A (Column number 1) the formula will return the same results as your current formula.

For the second scenario I wasn't as clear as I should have been. For your column headers, use a date instead of text to show your header. Enter the first of the month you want to display. Right click on the cell, choose Format Cell. On the number tab click on the "Custom" button. In the "Tpye" box enter "mmm" (no quotes). Click Okay. The field will now display as the first 3 letters of the month.

You can then use the second formula that I suggested.


Both methods will work.



Mike

 

[dchard]

Mike,

The first one did not work, at least I could not get it to work, but the second one does. Thank you.

Deb