# Sum all figures for a given month

1. ## Sum all figures for a given month

Hi all

I'm stuck with a formula and I wonder if anyone can help me.

I have a spreadsheet with 365 columns, one for each day of the year. What I would like to do is sum totals in a row which occur in a particular month.

So for example

Row A 29/1/20, 30/1/20, 31/1/20, 1/2/20
Row B 2 , 3 , 3 , 2

What I would like to do is sum row B by month.

So in this case would like the sum of January which equals 8.

I have tried hlookup but failed miserably

Cheers
Phil

2. ## Re: HLOOKUP help

HLOOKUP() only retrieves a single datum from a table. SUMIFS() is the function that will sum one range based on criteria from another range https://support.microsoft.com/en-us/...rs=en-us&ad=us =SUMIFS(reference to row B,reference to row A,">="&DATE(2020,1,1),reference to row A,"<="&DATE(2020,1,31)).

If this is more of a database type thing -- where you would have a good database of dates and values -- it could be preferable in the long run to arrange the data in a good database, then use tools like pivot tables to summarize the data. https://www.excel-easy.com/data-****...ot-tables.html

3. ## Re: HLOOKUP help

Welcome to the Forum, Phil.

HLOOKUP will only return a single value, so you can't use it to sum values.

You could use SUMIF if you had another row which included the month, or you could use SUMPRODUCT.

It would help if you attached a sample Excel workbook - the procedure for doing this is given in the yellow banner at the top of the page.

Hope this helps.

Pete

4. ## Re: HLOOKUP help

Welcome to the Forum grazey!

HLOOKUP will not give you what you want, which is why we encourage people to describe their problem rather than what they think the solution is.

First, your dates should be Excel dates, so hopefully that's what your data is in row 1 (A is a column, not a row).

Where in your data does it indicate that you want the sum for January? Suppose you have 1/1/2020 in cell C1. The formula to sum row 2 for January would be

Formula:
`Please Login or Register  to view this content.`

If this does not help solve your problem please see yellow banner at the top of the page and attach your file.

5. ## Re: Sum all figures for a given month

Hi there, firstly thanks for the speedy friendly answers.

I've had to wait until I got home from work as our firewall does not allow uploads.

Ok here's the spreadsheet. I need the formula to go in BH2. I would like it to sum all row 2 values which occur in February.

THanks very much!

Cheers
Phil

6. ## Re: Sum all figures for a given month

The most flexible way to do this is to put the month you are interested in in a cell, rather than baking it into a formula. That makes it easier to change later. I put 1/2/2020 in BH1 to represent the month you want, and it is displayed as Feb 2020. To use a different month, type in the desired month, using the first day of the month.

MrShorty's second suggestion is a good one if your data gets much more complicated than this.

7. ## Re: Sum all figures for a given month

Originally Posted by 6StringJazzer
The most flexible way to do this is to put the month you are interested in in a cell, rather than baking it into a formula. That makes it easier to change later. I put 1/2/2020 in BH1 to represent the month you want, and it is displayed as Feb 2020. To use a different month, type in the desired month, using the first day of the month.

MrShorty's second suggestion is a good one if your data gets much more complicated than this.
Great, that's perfect! thanks so much.

Phil

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1