# I am trying to get total amount by month

1. ## I am trying to get total amount by month

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

2. ## Re: I am trying to get total amount by month

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

3. ## Re: I am trying to get total amount by month

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.

4. ## Re: I am trying to get total amount by month

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

5. ## Re: I am trying to get total amount by month

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.

Untitled-1.gif

6. ## Re: I am trying to get total amount by month

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

Have a good day,

Kaper

7. ## Re: I am trying to get total amount by month

@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

8. ## Re: I am trying to get total amount by month

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

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