How to sum cells in a column that match an End of Month date in an adjacent column

[Monty85]

As the title suggests, is there a formula I can build that will Sum values in a column only if they match an End Of Month Date in a different column?

So the determination of the end of month would also need to be automatic and the end result would look something like below (only summing the values in green).

DATE	VALUE
10/07/2023	5
15/07/2023	10
31/07/2023	20
15/08/2023	25
31/08/2023	50
10/09/2023	10
30/09/2023	20
EXPECTED TOTAL	90

 

[Trebor76]

Hi Monty85,

I don't think you can do this in the one column but if you have another column you can:

Just make sure the dates in Col. A are actually dates not strings that look dates which considering the dates in your screen shot are left justified they might be.

Regards,

Robert

 

[SunnyAlv]

try

=SUM(SUMIF(A2:A8,UNIQUE(DATE(YEAR(A2:A8),MONTH(A2:A8)+1,0)),B2:B8))

 

[Monty85]

Thank you

Hi Monty85,

I don't think you can do this in the one column but if you have another column you can:

Just make sure the dates in Col. A are actually dates not strings that look dates which considering the dates in your screen shot are left justified they might be.

Regards,

Robert

try

=SUM(SUMIF(A2:A8,UNIQUE(DATE(YEAR(A2:A8),MONTH(A2:A8)+1,0)),B2:B8))

Thank you both. Both these solutions work.

 

[Trebor76]

Thank you both. Both these solutions work.

Thanks for letting us know and you're welcome

=SUM(SUMIF(A2:A8,UNIQUE(DATE(YEAR(A2:A8),MONTH(A2:A8)+1,0)),B2:B8))

SunnyAlv - very nice

 

[Peter_SSs]

You could also consider this shorter one

24 07 31.xlsm
	A	B
1	DATE	VALUE
2	10/07/2023	5
3	15/07/2023	10
4	31/07/2023	20
5	15/08/2023	25
6	31/08/2023	50
7	10/09/2023	10
8	30/09/2023	20
9		90
EOM Sum

Cell Formulas
Range	Formula
B9	B9	=SUM(B2:B8*(MONTH(A2:A8)<>MONTH(A2:A8+1)))

 

[Tom.Jones]

Hi,

use this formula:

=SUM((DAY(A2:A8)>27)*B2:B8)

in column A must be real date.

 

[Peter_SSs]

use this formula:

=SUM((DAY(A2:A8)>27)*B2:B8)

Unless I have misunderstood this...

build that will Sum values in a column only if they match an End Of Month Date in a different column

.. I don't think that formula will do what is required. In the below example only one date is an End Of Month Date yet your formula adds all the values.

24 07 31.xlsm
	A	B
1	DATE	VALUE
2	28/07/2023	5
3	29/07/2023	10
4	30/07/2023	20
5	30/08/2023	25
6	31/08/2023	50
7	28/09/2023	10
8	29/09/2023	20
9		140
EOM Sum (2)

Cell Formulas
Range	Formula
B9	B9	=SUM((DAY(A2:A8)>27)*B2:B8)