MATCH and OFFSET with dynamic range - Excel Formula

[inarobis]

Hello,

I would like to do the following with excel Formula.

these are the information

Sheet 2
A M
01/12/2001 24
01/06/2002 23
01/12/2002 25
01/06/2003 52
01/12/2003 53

there are already 2 range define in sheet 2 rngDate, rgnValues
How to obtain something like this with Excel formula and not VBA

Sheet1
Jun Dec tot
2003 52 53 105
2002 23 25 48
2001 24 24

I tried to use Match and Offset (but I have a problem with the
offset)

Any help will be really apreciate

Ina

 

[T. Valko]

I noticed that all of the dates are either June or December.

Assume on sheet1:

Column headers B11 = Jun, Dec, Tot
Row headers A2:A4 = 2003, 2002, 2001

Enter this formula in B2:

=SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)*6),--(YEAR(rngDate)=$A2),rngValues)

Copy across to C2 then down to row 4.

Enter this formula in D2 and copy down to D4:

=SUM(B2:C2)

 

[inarobis]

I noticed that all of the dates are either June or December.

Assume on sheet1:

Column headers B11 = Jun, Dec, Tot
Row headers A2:A4 = 2003, 2002, 2001

Enter this formula in B2:

=SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)*6),--(YEAR(rngDate)=$A2),rngV­alues)

Copy across to C2 then down to row 4.

Enter this formula in D2 and copy down to D4:

=SUM(B2:C2)

--
Biff
Microsoft Excel MVP














- Show quoted text -

Hello Thank you It is working perfectly
Only one question if I have monthly data ? can I adapt the formula
and I can use dynamic Range ?
thank you again
Ina

 

[T. Valko]

If you have monthly data and your column headers are like this:

B1:M1 = Jan, Feb, Mar, Apr .... Dec

Then change the formula to:

=SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)),--(YEAR(rngDate)=$A2),rngValues)


--
Biff
Microsoft Excel MVP

I noticed that all of the dates are either June or December.

Assume on sheet1:

Column headers B11 = Jun, Dec, Tot
Row headers A2:A4 = 2003, 2002, 2001

Enter this formula in B2:

=SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)*6),--(YEAR(rngDate)=$A2),rngV­alues)

Copy across to C2 then down to row 4.

Enter this formula in D2 and copy down to D4:

=SUM(B2:C2)

--
Biff
Microsoft Excel MVP














- Show quoted text -

Hello Thank you It is working perfectly
Only one question if I have monthly data ? can I adapt the formula
and I can use dynamic Range ?
thank you again
Ina

 

[inarobis]

If you have monthly data and your column headers are like this:

B1:M1 = Jan, Feb, Mar, Apr .... Dec

Then change the formula to:

=SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)),--(YEAR(rngDate)=$A2),rngVal­ues)

--
Biff
Microsoft Excel MVP










Hello Thank you It is working perfectly
Only one question if I have monthly data ? can I adapt the formula
and I can use dynamic Range ?
thank you again
Ina- Hide quoted text -

- Show quoted text -

Thank you so much It is working very well

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

If you have monthly data and your column headers are like this:

B1:M1 = Jan, Feb, Mar, Apr .... Dec

Then change the formula to:

=SUMPRODUCT(--(MONTH(rngDate)=COLUMNS($B2:B2)),--(YEAR(rngDate)=$A2),rngVal­ues)

--
Biff
Microsoft Excel MVP










Hello Thank you It is working perfectly
Only one question if I have monthly data ? can I adapt the formula
and I can use dynamic Range ?
thank you again
Ina- Hide quoted text -

- Show quoted text -

Thank you so much It is working very well