Hi

I use the following sheet for interest calculation (very simplified):

CoL
A beginning date
B end date (always in the same month as A)
C Interest amount calculation (using days between A and B)

In a different sheet I want to total the amounts in column C PER MONTH, but
the problem is that due to changing interest rates not every month has the
same number of amounts (that is rows). For example, April might have only one
amount (so in the same row A will be 01-apr and B 30-apr), but May 2 or 3 (so
01-May will be in a different row than 31-May, making it impossible to use
the VLOOKUP formula).

Does anyone know how to solve this, without adding an extra column to my
sheet?

Hans

Data list on Sheet1.
Say that Sheet2 has month and year in Column A.
Try this in ColumnB of Sheet2:

=SUMPRODUCT((TEXT(Sheet1!$A$2:$A$50,"mm/yy")=TEXT(A2,"mm/yy"))*(Sheet1!$C$2:
$C$50))

And drag down to copy as needed.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Hans" wrote in message
...
Hi

I use the following sheet for interest calculation (very simplified):

CoL
A beginning date
B end date (always in the same month as A)
C Interest amount calculation (using days between A and B)

In a different sheet I want to total the amounts in column C PER MONTH,
but
the problem is that due to changing interest rates not every month has the
same number of amounts (that is rows). For example, April might have only
one
amount (so in the same row A will be 01-apr and B 30-apr), but May 2 or 3
(so
01-May will be in a different row than 31-May, making it impossible to use
the VLOOKUP formula).

Does anyone know how to solve this, without adding an extra column to my
sheet?

Hans

Thanks, it works!

Hans

"Hans" wrote:

Hi

I use the following sheet for interest calculation (very simplified):

CoL
A beginning date
B end date (always in the same month as A)
C Interest amount calculation (using days between A and B)

In a different sheet I want to total the amounts in column C PER MONTH, but
the problem is that due to changing interest rates not every month has the
same number of amounts (that is rows). For example, April might have only one
amount (so in the same row A will be 01-apr and B 30-apr), but May 2 or 3 (so
01-May will be in a different row than 31-May, making it impossible to use
the VLOOKUP formula).

Does anyone know how to solve this, without adding an extra column to my
sheet?

Hans

AND thank you for the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Hans" wrote in message
...
Thanks, it works!

Hans

"Hans" wrote:

Hi

I use the following sheet for interest calculation (very simplified):

CoL
A beginning date
B end date (always in the same month as A)
C Interest amount calculation (using days between A and B)

In a different sheet I want to total the amounts in column C PER MONTH,
but
the problem is that due to changing interest rates not every month has the
same number of amounts (that is rows). For example, April might have only
one
amount (so in the same row A will be 01-apr and B 30-apr), but May 2 or 3
(so
01-May will be in a different row than 31-May, making it impossible to use
the VLOOKUP formula).

Does anyone know how to solve this, without adding an extra column to my
sheet?

Hans