0

I've been searching around and I am struggling to find why my formulas are not working.

Before I post my formulas I need to explain what I'm trying to do and it is a bit complicated (At least, this is the only way I can think of doing it).

**Background**
I need to create a spreadsheet on my division's budget (sorted by category), currently when I download the income/expense from our Finance system it just pumps out rows of data (RAW DATA), sorted by date.

  • Goal 1 - Have the exported RAW DATA automatically sort into categories which then a table shows clearly how much has been spent in each category ACHIEVED

  • Goal 2 - Have the exported RAW DATA sort into categories and ONLY show specific months based on the user's selection STUCK


RAW DATA EXPORT EXAMPLE:

TABLE: Data2015

Date    |  Description of Expense  |  Amount  |  Codes
Jan-15  |  Workshop for employee   |  100000  |  WRKS
Jan-15  |  Workshop for employee   |  100000  |  WRKS
Feb-15  |  Sponsors for employee   |  200000  |  SPON
Feb-15  |  Sponsors for employee   |  100000  |  SPON
Feb-15  |  Workshop for employee   |  300000  |  WRKS
Mar-15  |  Sponsors for employee   |  100000  |  SPON

If we use the above table as an example of the RAW DATA (Named "Data2015") I get when I export from our Finance systems (Which I converted to a table within Excel).
What I did to achieve Goal 1 was to create a new table (Named "Categories") with the descriptions of the categories.

TABLE: Categories

Categories | Codes
Workshop   | WRKS
Sponsors   | SPON

Then created another table (Named "Budget") like below:

TABLE: Budget

Categories | Codes | Amount
Workshop   | WRKS  | 500000
Sponsors   | SPON  | 400000



The formula I used for the Amount column on the Budget table is

=SUMIF(Data2015[Codes],[@Codes],Data2015[Amount])

So this formula helps me achieve Goal 1.

My attempt at achieving Goal 2, I tried the following.

Created a table to capture the months the user wants to see:

TABLE: Conditions

Month  | X | Background
Jan-15 | x | 1/01/2015
Feb-15 | x | 1/02/2015
Mar-15 | x | 1/03/2015

The user puts an "x" in the X column if they would like to see the expenses for that particular month.
I used this formula in the Background column:

=IF([@X]="x",TEXT([@Month], "d/mm/yyyy"),"")

I want to then further filter the months into categories like before.
So similar to the Categories table above, but for specific months based on what the user wants to see.
I managed to get it sort of working but only for 1 line, not the whole table.
I would like you to review my formula and let me know what I may be doing that is incorrect or maybe even my logic in achieving goal 2 is incorrect. Your insight will be very helpful and I apologise for the very confusing situation, I can't think of an easier way to explain.

I have created another spreadsheet that will mimic the Data2015 sheet with the following formula

=IF(ISNUMBER(SEARCH(Month_View!$D$5, $A5)),Data2015[@Account], IF(ISNUMBER(SEARCH(Month_View!$D$6,$A5)),Data2015[@Account],""))

Month_View! is where TABLE: Conditions is located and $D$5 is Jan-15, $D$6 is Feb-15 and so on.
This formula pretty much populates the table if it detects that A5 (Column where the date is) is the same as the TABLE: Data2015's Date column (on the same row, hence the @).

Up to this point it's all good, it works, so long as I have "Jan-15" or "Feb-15" in cell A5. To populate this cell based on the user's preference I used the following formula (which doesn't produce the result I want):

=IF(Condition[Background]=TEXT(Data2015[@Date],"d/mm/yyyy",Data2015[@Date],"")

So to me, that formula is meant to do the following:
IF the Date in the Data2015 table matches one of the dates/values (in the format of "d/mm/yyyy") in Condition's column named "Background" (Which will only show if the user put an "x" in the month they want to see) then fill this cell with the value from Data2015's Date column.
If the cell then populates with "Jan-15" or "1/01/2015" then the rest of the table will populate which I can then use to then further filter down by category with the formula I used to achieve Goal 1.

But the cell is not populating with "Jan-15" or "1/01/2015" instead it's showing blank or #VALUE!
It seems to be referencing the Condition table with @ as well which I don't want.

As confusing as it is... If you can understand what I'm trying to achieve, any insight or discussion will help. Maybe my brain is just too overloaded at this stage.

  • Michael Frank, thank you for the edit, converting my data into tables. – Wechikoma Sep 25 '15 at 1:48
0

I would use the Tables functionality built into Excel - no formulas are necessary. Select your range and hit Ctrl-T to get started.

This turns on Filters across the top row - you/your users can use them to filter Dates by Years and/or Months.

For the totalling functionality, add a Totals row (using the Table Tools / Design ribbon) and in that row you can choose what aggregation you want for each column (Sum, Count etc). This will automatically adjust to follow your filtering.

|improve this answer|||||
  • That would work however I believe my data includes categories which complicate things. I might have 10 transactions (say 2 x Jan, 3 x Feb, 5 x Mar) but the 2 in Jan, one of them is for Workshop and the other one is for Sponsorhips. Then the 3 in Feb, 1 of them is for Workshop and the other 2 are for Travels, then the 5 in March, all 5 could be for workshops. I need to be able to see the totals for the Workshops only (either all months or individual months) and also have the ability to filter by month. – Wechikoma Sep 25 '15 at 5:58
  • So ... filter the Category to Workshops only. Then filter by month or not. – Mike Honey Sep 25 '15 at 6:19
0

Okay so I've solved it!

Here is the formula I used:

=SUM(SUMIF(Data2015BG1[Category],[@Codes],Data2015GB1[Amount]))

*PS: Sorry if the references is different to the examples given in my question.

The Data2015BG1 sheet has formulas that detects whether the Trigger column has an "x" in it or not and presents the necessary data based on the month selected. The formula I used is:

=IF(ISNUMBER(MATCH(TEXT(Data2015[@Period],"d/mm/yyyy"),Condition2015A[Background],0))=TRUE,Data2015[@Period],"")

Hope that makes sense, I would have posted pictures to show you exactly what I managed to achieve visually because essentially what I did was made things easier for anyone to use, but I don't have enough reputation to post images yet! Maybe next time.

Filters would have worked but I'm creating this for people who pretty much have not much experience in Excel so even asking them to filter is a bit much (... I know, I know...)

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.