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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11/19/2017[/TD]
[TD="align: right"]11/26/2017[/TD]
[TD="align: right"]12/3/2017[/TD]
[TD="align: right"]12/10/2017[/TD]
[TD="align: right"]12/17/2017[/TD]
[TD="align: right"]12/24/2017[/TD]
[TD="align: right"]12/31/2017[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]434[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]286[/TD]
[TD="align: right"]366[/TD]
[TD="align: right"]464[/TD]
[TD="align: right"]144[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]329[/TD]
[TD="align: right"]206[/TD]
[TD="align: right"]174[/TD]
[TD="align: right"]441[/TD]
[TD="align: right"]136[/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]320[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]238[/TD]
[TD="align: right"]118[/TD]
[TD="align: right"]226[/TD]
[TD="align: right"]401[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]173[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Nov[/TD]
[TD="align: right"] 4,139[/TD]
[TD="align: right"][/TD]
[TD="align: right"]356[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]196[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]460[/TD]
[TD="align: right"]378[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]263[/TD]
[TD="align: right"]335[/TD]
[TD="align: right"]371[/TD]
[TD="align: right"]225[/TD]
[TD="align: right"]310[/TD]
[TD="align: right"]196[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]427[/TD]
[TD="align: right"]207[/TD]
[TD="align: right"]401[/TD]
[TD="align: right"]343[/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]437[/TD]
[TD="align: right"]394[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]446[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]492[/TD]
[TD="align: right"]193[/TD]
[TD="align: right"]385[/TD]
[TD="align: right"]369[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]372[/TD]
[TD="align: right"]195[/TD]
[TD="align: right"]191[/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]124[/TD]
[TD="align: right"]358[/TD]
[TD="align: right"]270[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2704[/TD]
[TD="align: right"]1435[/TD]
[TD="align: right"]1985[/TD]
[TD="align: right"]2684[/TD]
[TD="align: right"]1449[/TD]
[TD="align: right"]2952[/TD]
[TD="align: right"]2244[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"]B5[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH($D$1:$J$1)=MONTH(A5 & "/1"))*D2:J9)[/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10"][/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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