CODEWITHSUNDEEP
excelexcel-formula
noname738
noname738

Reputation: 61

How to increment a specific number in a formula when dragging formula across a row

I'm using the following formula:

 =SUMPRODUCT(--(MONTH($B$2:$B$700)=3),--('[$O$2:$O$700="CDE"))

I want to drag it across a row and automatically change the spot where the '3' is every time for each month.

How can I increment the month number in this formula?

We have columns with the following headings like:

Jan-15 Feb-15... Dec-15

So I've tried to use 

 =SUMPRODUCT(--(MONTH($B$2:$B$700)=MONTH(A1)),--('[$O$2:$O$700="CDE"))

A1 in the upper formula being the Cell number for January, B1 for February, etc. Theoretically this should work but I keep getting 0 as my result.

Upvotes: 1

Views: 543

Answers (2)

user4039065
user4039065

Reputation:

The COLUMN function returns the numerical index.

=SUMPRODUCT(--(MONTH($B$2:$B$700)=COLUMN(C:C)),--('[$O$2:$O$700="CDE"))

Upvotes: 2

Dan Donoghue
Dan Donoghue

Reputation: 6206

Try Column(A1) instead of Month(A1) (Although Month should work assuming your data is a proper date and not a textual one).

Upvotes: 2

Related Questions