Adding values based on both month and text criteria

**3smees23** · 09-02-2011, 07:26 AM

Hi,

I'm trying to create a savings spreadsheet. I have downloaded 6 months worth of data from my online banking and have put them in separate tabs.

I have entered a category in the first column of every tab. This is to break out what the transaction/withdrawal was for i.e. rent, food, social..and so on.

I have attempted a formula in 'sheet1' but its still fairly manual. Please can some one write a formula that adds the different values for each catagory, depending on date and text.

Please see the attached. It should be fairly self explanatory.

Thanks in advance

Sam

**Domski** · 09-02-2011, 07:39 AM

Hi,

How about a pivot table like in the attached. All you'd need to do would be to add each month's data onto the Combined sheet and then refresh the range the pivot table looked at which could be done automatically with a little bit of vba code.

Dom

**teylyn** · 09-02-2011, 07:47 AM

Hello,

If you make sure that the sheet names are EXACTLY the same as the text in row 2, then you can use a formula like this in cell J3 and copy it down and across:

=SUMIF(INDIRECT(J$2&"!A2:A100"),$I3,INDIRECT(J$2&"!E2:E100"))

But you will have to fix the inconsistent naming first. For example, the sheet is called "Aug " with a trailing space, the value in the cell is just "Aug" in your file. I've fixed that in the attached example. You will still need to fix the Apr vs. April and Mar vs. March.

The #Ref! values in the attached file mean that the sheet "Apr" or "Mar" cannot be found. Rename the sheets from "April" to "Apr" and from "March" to "Mar" and the results will show fine.

In Excel, little details like consistent spelling DO matter.

cheers,

**Richard Buttrey** · 09-02-2011, 08:12 AM

Hi,

See the attached

A better approach is to use a single sheet to hold all your records. i.e. the Data sheet in the attached.

There are some categories missing from the original data summary and I've added some. However others are still missing so you'll need to go through and ensure they are listed.

There are also rows in the original monthly sheets (and since I've just copied them the Data sheet), where there are obviously debits or credits missing where the Running balance changes. e.g. February E13.

You'll need to correct these too.

Regards