Somma in base a periodi

lourid · #1 02-27-2022, 03:33 PM

Ciao

a tutti avrei necessita' di sapere se esiste una formula
per sommare dei valori in base a delle date allego file
nel range D2:O5 i valori
A2:b5 le date
D1:O1 i mesi
colonna P i totali attualmente inserisco a mano
le righe potrebbero essere piu di 2000

p45cal · #2 02-28-2022, 02:52 PM

Check your calculations; cell P2 is calculated one way, but cell P5 is calculated differently:
You have in cell P5:
=(F5/21*31)+(G5/4*30)
which becomes >70k. The 2 cells for March and April contain 7k and 8k, which only add up to 15k. How can you get 70k from those 2 cells?
I think your formula in cell P56 should be:
=(F5/31*22)+(G5/30*4)
which calculates to 6,034.41 , is that correct?

lourid · #3 03-01-2022, 01:37 AM

Sorry you are right, the calculation and amount / day month x n. days worked

p45cal · #4 03-01-2022, 07:26 AM

Formula in row 2:

Code:

=CHOOSE(MIN(MONTH($B2)-MONTH($A2)+1,2),INDEX($D2:$O2,MONTH($A2))*(DAY($B2)-DAY($A2)+1)/DAY(EOMONTH($A2,0)),INDEX($D2:$O2,MONTH($A2))*(1-(DAY($A2)-1)/DAY(EOMONTH($A2,0)))+INDEX($D2:$O2,MONTH($B2))*DAY($B2)/DAY(EOMONTH($B2,0))+IF(MONTH($B2)-MONTH($A2)>1,SUM(INDEX($D2:$O2,MONTH($A2)+1):INDEX($D2:$O2,MONTH($B2)-1)),0))

Copied down.
See attached. (Row 6 was my test row.)

lourid · #5 03-01-2022, 11:47 AM

ok perfect thanks a lot

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Return value base on two columns	yewee	Excel	5	02-13-2017 03:16 AM
finding variations in the data base	adryan	Excel	4	07-15-2015 12:19 AM
Creating small data Base	quintinros	Excel	2	06-26-2014 12:28 AM
Calculate formula base of list menu	rkeles	Excel	4	09-22-2010 12:38 AM
Calendar Base	prayojana	Project	0	11-12-2007 01:13 AM