Hi,
I'm attempting to sum a table with the following format:
COL A COL B COL C-COL N
GL Code GL Description Jan-Dec GL Code Value
I want the formula to look up the GL Code or Description and then return the YTD value (i.e. for March it will return the sum of COL C,D,E and then when the month changes it will sum C,D,E,F and so-forth).
Is there a forumla to easily do this?
can you upload a sample file? It would easier to understand and you will get faster your asnwer.
Hi,
hopefully this has attached.
Attempting to sum in the third tab based on Code/Description and whichever month in the year we're in.
Thanks
in C4 and copy down.
=SUMIF('2019'!$E$2:$P$2,"<="&$C$3,'2019'!E3:P3)
Thanks - I had probably oversimplified my example workbook too much.
I've reattached with more detail, the SUMIF provided is fundamentally what I'm trying to achieve but on Tab3 the formula would reference the Code where I'm breaking out the details and then consolidate the rest based on Column C in Tab2.
So the formula would still return the data up until the given date (March in this case) but also look at the "Code" (Col A), then the "Group" (Col C) in Tab2 to return the consolidated SUM.
Thanks for this one, this has been useful for my current solution!Originally Posted by torachan
in C4 and copy down.
=SUMIF('2019'!$E$2:$P$2,"<="&$C$3,'2019'!E3:P3)
Hi,
See the answer in D15
Great, that's perfect for the lines where I'm returning the individual GL code.
If you have a solution for returning the combination i.e. everything grouped as an "Expense" or "Direct Cost" in Column C of Tab2 that would also be really helpful!
So you would like to have the sum of all expense /overhead/ direct cost etc. for the month specified YTD.(Jan-Apr for the date 30.04.2020?)
If so - it is complacting things by far. I'm not sure I know how to do that...perhaps one of the experts of the forum will know how to solve this.
@tommpalmer123
You can use a pivot table to analyse the data.
If you want to use pivot table (to the fullest) you need to restructure the data in rows instead of columns.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thanks for the replies guys,
Belinda200 - yes I would like to do this. For Sales I want to split the GL codes out and sum to the given month, for expenses I want to consolidate as there are a lot of GL lines
oeldere - I did think that but I want to do the same for 2020 and have a neat summary page for distribution so the lookup/sum I'm trying to achieve would be preferable. The forumula Belinda provided for the GL codes worked, I'm just wondering if there is a way to manipulate it slightly so that it effectively SUMIFS all the overheads up to March 19 (then April, May etc etc)?
I added an example based on your data 2019.
See the attached file.
Thanks Oeldere,
Given the data pretty much comes in the format I have in my spreadhseet, my backup option is to have the SUMIF for the Group level data in the 2019 spreadhseet then lookup in my summary tab (Tab3). Not as clean but does the job.
If there is a formula to do it directly in tab3 without having the sumif in tab2 that would be interesting to see but I can use this workaround if not!
"Given the data pretty much comes in the format I have in my spreadhseet"
You import this from another source?
Yes the output on the 2019 tab broadly comes in that format
A little primitive - but I applied a formula that is supposed to work , please see in D73
your data for 2019 with a pivot table.
Thanks Oeldere,
For the full picture I have attached an example of the summary I'm working towards. The OFFSET formula used earlier in the thread works for getting the 2020 and 2019 sales data from the relevant data sets.
Your Pivot model will definitely work for the remainder (Direct Costs, Overhead, Expenses etc. in the Yellow cells) and I can certianly use that. It would just be interesting to see if there is a formula that can look up these grouping codes in the data set tabs and "SUMIF" them based on said code and what the month-end value is (to save having more sheets in the workbook). Otherwise your solution will work fine
Last edited by tommpalmer123; 04-27-2020 at 04:41 PM. Reason: Add attachment
Please repy if this is the result you are looking for.
See the green cells in the 3th sheet.
F4 =SUMIF('2019'!$B$3:$B$69,$E4,'2019'!E$3:E$69) and drag across (for the light green cells).
Yep that's more or less what I'm doing at the moment and using that to bring into my summary page (which works and will be fine for the purpose of what I'm putting together).
The formula you have worked on in CellD73 of the 2019 tab is what I was hoping would be possible as a workable formula but that doesn't seem to be able to pick up the values so it might not be an option!
Wonder if a SUMIF/SUMPRODUCT variant on the formula in D73 might work? USing SUMIF I can get it to return 0 rather than #N/A which suggests something is working but might just be a false lead!
for your first comment.
It would be helpfull to know you already had that solution.
Now forummember spend time on a solution you already had.
If it is important to have the cummulated values till an certained month you can use:
the methode of belinda200
a pivot table (but then you have to re-arange your data)
edit
And you did also not reply on the solution of torachan; offering the solution with sumif.
If forummembers post a solution, it would be nice if you take the time to respond to that item.
Last edited by oeldere; 04-27-2020 at 05:52 PM.
tommpalmer123 - did you see my file in post #17?
I think it can give you the resolution.
Here is the summary.
Last edited by Limor_OP; 04-27-2020 at 06:57 PM.
Yes I did, thank you.
This is definitely another solution I can use
You're welcome tommpalmer123, and thanks for the feedback.
c4 = =SUMIF($E$2:$P$2,"<="&$B$1,$E4:$P4) and drag down.
See the orange cells in the attached file.
Thanks Belinda, Oeldere and thank you for the time you've spent looking at this,
Both of these options will work as well.
Glad I could help.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Also I managed to have a formula to be consolidated for both situations (either searching by code or by group. so you can drag the formula down now.
Please see attached.
Last edited by Limor_OP; 04-28-2020 at 03:43 AM.
That's great, thank you very much!
@Belinda200
your solution
Great job.{C$72=$N$2;(SOM(ALS($C$3:$C$69=$B73;$E$3:$N$69)));ALS(C$72=$O$2;(SOM(ALS($C$3:$C$69=$B73;$E$3:$O$69)));ALS(C$72=$P$2;(SOM(ALS($C$3:$C$69=$B73;$E$3:$P$69)));""))))))))))))}
You use a lot if statement, which makes it hard to understand en to change if it's necessary.
It also could take a lot of time to calculate on a big file.
But again, great job.
oeldere - thank you for the compliment! I appreciate it, I agree that it's not the ideal solution, that was also what I wrote when I uploaded the file, and said it was a little primitive, as I gave an IF formula for each 12 scenarios (1 for each month). That's the only way I found to accomplish that without having a PIVOTABLE in action.
since I'm familiar with the trial balance report - I assume that the structure of the report is quite solid, columns will always remain Jan-Dec, and lines are not expected to change massively, only the values are varying each period - so I guess no great changes are needed to be done.
Thanks again, great honor hearing that from an expert user
Last edited by Limor_OP; 04-28-2020 at 04:39 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks