I am trying to get total amount by month

**amm_manaz** · 02-03-2014, 11:16 AM

I have done a shopping cost sheet, which obviously has the date column in DD/MM/YY format. I have "Purchase Item type" and "Amount" column as well, I have a separate table to get Monthly Cost by Purchase Item Type.

I am not sure how to get the Total Amount by Purchase Item Type by the Month;
Eg How much I have spend in Groceries, Vegetables, etc.

Which formula I could use?

I have got the whole total amount till January to now by this formula;
=SUMIFS(Table1[[#All],[Amount]],Table1[[#All],[Purchase Item Type]],[@[Purchase Item Type]])
Need further help; thanks

**Kaper** · 02-03-2014, 11:23 AM

I'd suggest using Pivot Table instead of formula in such case.

**amm_manaz** · 02-05-2014, 06:00 AM

Thanks, I have tried, yet its working. Still; what could be the formula to get (Condtion) to get the detail. If I do not want to use pivot table, but yet if i want to get the same result, what could be sumif condition i need to use.

**Kaper** · 02-05-2014, 07:23 AM

try to use two more conditions in your sumifs
for instance for October 2013:
Table1[[#All],[Purchase Date]],"<" & DATE(2013,11,1)
and
Table1[[#All],[Purchase Date]],">=" & DATE(2013,10,1)

so the whole formula would be
=SUMIFS(Table1[[#All],[Amount]],Table1[[#All],[Purchase Item Type]],[@[Purchase Item Type]],Table1[[#All],[Purchase Date]],"<" & DATE(2013,11,1),Table1[[#All],[Purchase Date]],">=" & DATE(2013,10,1))

of course date(s) can be stored in some cell.
some further functions to be included in the formula could be TODAY() and EOMONTH

**amm_manaz** · 02-05-2014, 05:32 PM

Thanks Kaper, I have tried the same code, as I have done some adjustment. Its not showing in the following table which I have created. its only showing as '0' values in £. My date (You put as Purchase Date) "Date" as it is in the following format; "01/01/14", And I started my table with the starting date from this year January.not sure what is causing the trouble. I have attached the sheet for you to checkit out. I am trying to get the whole basics, rather doing everything very simple and easy as using pivot table. Thanks you help me out with the pivot table. as I already created and found it very much easy.

Please help me out with it. Many thanks
Untitled-1.gif

**Kaper** · 02-06-2014, 01:43 AM

Honestly, I'd strongly recommend reading forum rules and focusing on "Want to get your question answered quickly?" section.

Have a good day,

Kaper

**vlady** · 02-06-2014, 01:53 AM

@amm_manaz Welcome to the forum. What Kaper is saying..."post a real workbook" we don't want Kaper or any other member who want's to help you to redo the workbook based on your picture

so..

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**amm_manaz** · 02-06-2014, 11:54 AM

Thanks a lot guys, I have tried this code; and it worked.

=SUMIFS(Table1[[#All],[Amount]],Table1[[#All],[Purchase Item Type]],[@[Purchase Item Type]],Table1[[#All],[Date]],">=1/1/2014",Table1[[#All],[Date]],"<=31/1/2014")

Thanks to Kaper; I am going to use pivot table which is make the job much easier. I have learn something on the ways; thanks

I am trying to get total amount by month

LinkBack

Thread Tools

Rate This Thread

Display

I am trying to get total amount by month

Re: I am trying to get total amount by month

Re: I am trying to get total amount by month

Re: I am trying to get total amount by month

Re: I am trying to get total amount by month

Re: I am trying to get total amount by month

Re: I am trying to get total amount by month

Re: I am trying to get total amount by month

Thread Information

Users Browsing this Thread

Similar Threads

how to work out rate based on total time and total amount

[SOLVED] Returning total amount earned in a month by day of the week

Validation by Total Amount

transfer cell $ amount to other sheet month-to-month without overc

[SOLVED] How do I calculate Amount of Sales Tax from Total Amount?

Bookmarks

Bookmarks

Posting Permissions