How to Sum Cells that comply with a date(month and year) and account ID

[czhl1]

HI GUYS,

I need help with this problem:


In a worksheet i have all the transactions of income and expenses. Each transaction has been classified with date, account ID, description and amount of the transaction.

In another worksheet i have a summary where all the months of the year as a column and each account as a row, to get each Income or expense classified according to the account ID and month of execution.

What i need in the summary is a sum of all the transactions done during that month for each specific account ID.

Here are portions of the worksheet so you can see what i mean.

FECHA ACCOUNTID DESCRIPCION GASTOS INGRESOS TOTAL
02/01/2013 900101 Pago de mantenimiento Local 05 mes de Enero 2,408.70 2,408.70
05/01/2013 900101 Pago de mantenimiento enero de Local 06 4,828.80 7,237.50
08/01/2013 905452 Emitido a nombre de DICOM por compra 96.00 7,141.50
08/01/2013 906452 Emitido a nombre de XXXXX por compra 196.00 6,945.50



ene-13 feb-13 mar-13 abr-13 may-13
ID CUENTA EXPENSES - - - - -
910100 SUELDOS Y BENEFICIOS - - - - -
910101 Sueldos y Salarios
910102 Vacaciones
910103 Horas Extras
910104 Bonificaciones
910105 Comisiones
905452 PAPER AND SOAP
906452 COMPUTER MAINTENANCE

[FDibbins]

Hi and welcome to the forum

Often, a copy/paste of your data to here doesnt really work too well - and this is 1 of those times.

From the sounds of it, you need to use sumifS(), but its hard to say without seeing a sample of what you are working with.

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[czhl1]

Here is the xlsx file.

THANK YOU FOR YOUR QUICK REPLY

[popipipo]

I think a pivot table is the most suitable for this

[czhl1]

Thanx Popipipo.

I still would prefer a formula that looks for month, year and account ID and sums only the transactions with that code and places them on the other sheet ordered by month.

I dont want to change the formatting on the sheet that sums it all up.

Or is there a way to pull all the data from the pivot sheet?

Im open to any suggestions...

[FDibbins]

See if the attached will give you what you want?

[czhl1]

Thanx buddy, thats exactly what i needed, you awesome.

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)

[newdoverman]

I don't understand the language of the worksheet but for what I did understand I filled in formulae. I used two helper columns in order to shorten the formulae needed to match criteria. You should be able to fill in the rest of the formulae following the pattern of the formulae given.