MATCH and OFFSET with dynamic range - Excel Formula

  • Thread starter Thread starter inarobis
  • Start date Start date
I

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 :D

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

Assume on sheet1:

Column headers B1:D1 = 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)
 
I noticed that all of the dates are either June or December.

Assume on sheet1:

Column headers B1:D1 = 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 -
Click to expand...

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 :D
Ina
 
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 B1:D1 = 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 -
Click to expand...

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 :D
Ina
 
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 :D
Ina- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you so much :D It is working very well
 
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 :D
Ina- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you so much :D It is working very well
 
Back
Top