How can I get a monthâ€™s total amount payable in a cell, using excel function, based on a table where amount payable from a date in a month are noted. Suppose Cell A2:A6 are Jan/2010, Feb/2010 &march/2010 and so on. In Cell B2:B6 I have to get total amount payable for the related months using a table Where M2:M6 are used for entering dates from which amount payable and in N2:N6 the rate of amount payable are stated (Example: M2=01/01/2010 & N2=2500, M3=25/01/2010 & N2=3000, M3=15/02/2010 & N3=2000, M4=15/03/2010 & N4=4000. I want to get total amount payable for each month in cell B2:B6)

in O2 you could put a formula: =MONTH(M2) ....copying down the rest of the column so O3 is =MONTH(M3) etc... Then in your B2, B3, B4....fields you could put a sumif.

i.e. in B2 put =SUMIF(O:O,1,N:N)

where the O:O is the the entire column that show the numerical month value, the 1 is the month you are summing, and the N:N is the entire column that has your dollar values to sum.in B3 the formula would be the same, just change the 1 to a 2 because you are looking for February (2nd month) totals...B4 change it to 3 for March and so on.

Also, once the formula is put it all of O, you can hide it to not see it.

That work for you?

thanks for the response.You misunderstand my question I think the question was not put in a clear way.. I may explain: The amount payable from a date is a rate for the month for the given dates. For example pay on 01/02/2010 is 20000, and on 16/02/2010 is 25000, means that i have to pay Rs@20000 for 15 days and @25000 for 13 days in 02/2010. Hence the formula you offered has to be changed .Looking forward

the curator

Hi, Not an easy one to work out!

To do this, I extended your table in M3 to N6, so that I could calculate the number of days at each rate.

In cells O1 to S2 I entered the start and end dates for each month.

The blank table now looks like this:M N O P Q R S 1 01/01 01/02 01/03 01/04 01/05 2 From Rate 31/01 28/02 31/03 30/04 31/05 3 01/Jan/10 2500 4 25/Jan/10 3000 5 15/Feb/10 2000 6 15/Mar/10 4000

Make sure the start and end dates are full Excel dates with year - I shortened the format, to make it fit on this page.

To calculate the days in each period I entered this formula in cell O3:

=IF($M4="",IF($M3>O$2,0,IF(AND($M3>=O$1,$M3<=O$2),O$2-$M3+1,O$2-O$1+1)),

IF($M4<O$1,0,IF(AND($M3<O$1,$M4<=O$2),$M4-O$1,IF(AND($M3<O$1,$M4>O$2),O$2-O$1+1,

IF(AND($M3>=O$1,$M4<=O$2),$M4-$M3,

IF(AND($M3>=O$1,$M3<=O$2,$M4>O$2),O$2-$M3+1,0))))))

Note that the formula has been split on to several lines for ease of viewing - it will need to be made into one line before pasting into the relevant cell.

Note also the $ signs - they are required to enable the formula to be dragged and extended to cell S6So, drag the formula to extend it to O6, then select all four cells O3 to O6, and drag them across to column S

Cell S6 should now contain this:

=IF($M7="",IF($M6>S$2,0,IF(AND($M6>=S$1,$M6<=S$2),S$2-$M6+1,S$2-S$1+1)),

IF($M7<S$1,0,IF(AND($M6<S$1,$M7<=S$2),$M7-S$1,IF(AND($M6<S$1,$M7>S$2),S$2-S$1+1,

IF(AND($M6>=S$1,$M7<=S$2),$M7-$M6,

IF(AND($M6>=S$1,$M6<=S$2,$M7>S$2),S$2-$M6+1,0))))))The table now looks like this (I included totals on row 7 just to show that the correct number of days had been calculated for each month:

M N O P Q R S 1 01/01 01/02 01/03 01/04 01/05 2 From Rate 31/01 28/02 31/03 30/04 31/05 3 01/Jan/10 2500 24 0 0 0 0 4 25/Jan/10 3000 7 14 0 0 0 5 15/Feb/10 2000 0 14 14 0 0 6 15/Mar/10 4000 0 0 17 30 31 31 28 31 30 31To get the monthly pay, either use a formula like this (in cell M8):

=(O3*$N3)+(O4*$N4)+(O5*$N5)+(O6*$N6)or modify the main formulas to show pay rather than days.

The formula in cell O3 becomes:

=IF($M4="",IF($M3>O$2,0,IF(AND($M3>=O$1,$M3<=O$2),O$2-$M3+1,O$2-O$1+1)),

IF($M4<O$1,0,IF(AND($M3<O$1,$M4<=O$2),$M4-O$1,IF(AND($M3<O$1,$M4>O$2),O$2-O$1+1,

IF(AND($M3>=O$1,$M4<=O$2),$M4-$M3,

IF(AND($M3>=O$1,$M3<=O$2,$M4>O$2),O$2-$M3+1,0))))))*$N3

Drag and extend it as before.

The totals on row 7 will now be monthly pay rather than days in the month.

Cell O7 is just =SUM(O3:O6)01/01 01/02 01/03 01/04 01/05 31/01 28/02 31/03 30/04 31/05 60000 0 0 0 0 21000 42000 0 0 0 0 28000 28000 0 0 0 0 68000 120000 124000 81000 70000 96000 120000 124000This approach should work even if the table is extended in either direction.

I have only tested this on the limited data you posted, so you will need to test it with more data. In particular look at data that falls at the limits, such as when a pay rate changes on the first or last day of the month.

Regards

thank you very much . I am going to check the logic . After the test, surely i will reply back

sincerely

thecurator

Ask Your Question

Weekly Poll