Excel 2007 formula sum according to month

[F0RE5T]

Excel 2007 formula required please.

The first row (D1:Z1) has an end of week date (format i.e. 19/11/17, 26/11/17) spread over multiple columns.

Eight rows lower (D10:Z10) is a currency total for that column, i.e. £20.00

I have a field (A5) that allows a month name i.e. Nov to be selected from a dropdown list

What I need to do is select a month (A5) and the currency totals for that month are calculated and placed in A9.

Hopefully that makes sense and any questions please ask, and thanks in advance for any help.

Forest

 

[AlanY]

Re: Excel 2007 forumal sum according to month

try

=SUMPRODUCT(--(MONTH(D1:Z1)=MONTH("1/"&A5)),D10:Z10)

 

[F0RE5T]

Re: Excel 2007 forumal sum according to month

try

=SUMPRODUCT(--(MONTH(D1:Z1)=MONTH("1/"&A5)),D10:Z10)

Perfect and many thanks.

Q. what does the "MONTH("1/"&A5)" do?

I will now see how I could calculate how many columns totals were included i.e. 3 weeks had costs and 1 week did not. The value would be put in another cell so separate formula.

You are a star

 

[jim may]

Re: Excel 2007 forumal sum according to month

Excel 2010

	A	B	C	D	E	F	G	H	I	J
1				11/19/2017	11/26/2017	12/3/2017	12/10/2017	12/17/2017	12/24/2017	12/31/2017
2				434	106	203	286	366	464	144
3				329	206	174	441	136	153	320
4				238	118	226	401	155	385	173
5	Nov	4,139		356	180	165	196	144	460	378
6				102	263	335	371	225	310	196
7				427	207	401	343	106	437	394
8				446	160	290	492	193	385	369
9				372	195	191	154	124	358	270
10				2704	1435	1985	2684	1449	2952	2244

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B5 =SUMPRODUCT((MONTH($D$1:$J$1)=MONTH(A5 & "/1"))*D2:J9) <tbody> </tbody>

<tbody>
</tbody>

 

[AlanY]

Re: Excel 2007 forumal sum according to month

Perfect and many thanks.

Q. what does the "MONTH("1/"&A5)" do?

I will now see how I could calculate how many columns totals were included i.e. 3 weeks had costs and 1 week did not. The value would be put in another cell so separate formula.

You are a star

you're welcome

as you've month names in A5, to add 1/ in front of the month name is to trick excel to treat A5 as date rather than text, so that the month() function can work on it

 

[F0RE5T]

Re: Excel 2007 forumal sum according to month

you're welcome

as you've month names in A5, to add 1/ in front of the month name is to trick excel to treat A5 as date rather than text, so that the month() function can work on it

Thank you for the explanation and help.

Forest

 

[F0RE5T]

Re: Excel 2007 forumal sum according to month

Hi Many thanks for your effort and answer and I will look at the difference between both answers.

Again many thanks

Forest