Quarterly/YTD Calculation

[Laci07]

Hello! I need assistance creating a calculation (in a new table) based on data in a Pivot table to Sum quarterly (Year-to-Date) counts of Actuals IF we've reached the end of a particular month, as such:

PIVOT TABLE
MONTH/YEAR AAA BBB CCC Grand Total
18-Nov 1.1 8.8 3.3 13.2
18-Dec 2.2 7.7 4.4 14.3
19-Jan 3.3 6.6 5.5 15.4


In another Table, I want to (automate) the Quarterly percentage of ACTUAL headcount not Forecast (although populated) only IF we've reached EOMonth by Dept:
1Q = Nov/Dec/Jan 2Q = Feb/Mar/Apr 3Q =May/Jun/Jul 4Q =Aug/Sep/Oct, and Fiscal YTD.
In other words, I only want to Sum counts for Nov thru June but update/include July numbers once they become Actual. 4Q should remain Blank until Aug numbers are reported after Aug 31st. To that end, I was trying to create a SumIF or SumProduct/EOMonth/RoundUp statement/formula to group the data (%) by Quarters and Fiscal YTD but couldn't get it to work.

NEW TABLE/END RESULT
TYPE 1Q19 2Q19 3Q19 4Q19 FY19YTD
AAA 10% 20% 10% 20% 40%
BBB 20% 30% 20% 30% 70%
CCC 30% 15% 30% 15% 75%


FORMULA USED:
=SUMPRODUCT((ROUNDUP(MONTH($B$5:$B$7)/3,0)=ROWS($C$5:C5))*$G$5:$G$7)

Thanking you in advance for your time and consideration.

[AliGW]

Welcome to the forum.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Laci07]

=iferror(sumproduct(($a$5:$a$12=columns($a$1:a$1))*($c$4:$f$4=$p4)*($c$5:$f$12))/sumproduct(($a$5:$a$12=columns($a$1:a$1))*($g$5:$g$12)),"")

[AliGW]

We do not need nor do we ask to see your real data - just a sample file mocked up manually. You can create that at home and upload it. Read my instructions again, which make this clear.

Your 'basic' information is a bit too basic. What you have failed to show us are the formulae that you have tried and failed to get to work. Share those and we may be able to make a small tweak to them so that they work for you. You will probably need to make use of the TODAY() function, but I am not going to guess at a formula.

Please provide the formulae requested.

[Laci07]

Hello I provided the additional information requested.....

[AliGW]

Where are the formulae I asked to see, please?

[Laci07]

Formula used:
=sumproduct((roundup(month($b$5:$b$7)/3,0)=rows($c$5:c5))*$g$5:$g$7)

[JeteMc]

If I understand:
1. A helper column, which may be hidden for aesthetic purposes, will assign the quarter.
The helper column may be populated using: =CHOOSE(MONTH(B5),1,2,2,2,3,3,3,4,4,4,1,1)
2. The quarterly values may be populated using:

Please Login or Register  to view this content.

As you are not allowed to upload files, please make specific references to the file included*, assuming you are allowed to download, so that we can make the appropriate adjustments.
*As in: "The value in cell K5 should be... because..."
Let us know if you have any questions.

[Laci07]

Thanks a million! The formula worked Perfectly

[Laci07]

Is there a more efficient way to calculate Fiscal YTD (Column O in this example) other than =SUM(K5:N5), which returns an error if I adjust Sliders to only see 1QTR (and not 2QTR or 3QTR)???

[JeteMc]

I can not replicate the issue.
First I can't produce a slicer for the table in columns J:O that would allow me to only see 1QTR. The slicer choices I get are: TYPE, 1Q19, 2Q19, 3Q19, 4Q19 and FY19YTD.
Second when I delete the columns 2QTR:4QTR (which I assume the slicer does) the FY19YTD column still yields answers.
It may help if you provide more information, however in this instance not being able to see a sample limits my understanding of the problem.
Sorry not to be of more help.

[Laci07]

see attached Screen prints of Examples.
I hope this attached correctly

FORMULA USED IN FYTD Column (returns #Value! Error):
=SUMIFS(INDEX($LC24:$LK$30,MATCH($N$4,$LB$25:$LB$30,0),),$LC$24:$LK$24,”<=”&$A$1,$LC$4:$LC$15,”>=”&DATE(YEAR($LC$4),1,1))

Thanks much!

[AliGW]

Please attach the Excel workbook - not an image of it.

[JeteMc]

As Ali states I would really need to see the .xlsx file from which the screen shot was taken. The screen shot displays a table in the range M4:R7. The formula references ranges and cells that are not shown (except cell N4), so I don't know what is in those cells to be able to troubleshoot the issue.
A couple of things that the screen shots do show:
1. In the top screen shot R4 populates while R5:R7 display errors. This sometimes indicates that the formula should be array entered. Select cell R4, Press the F2 key, Simultaneously press the Ctrl, Shift and Enter keys, drag the fill handle down.
2. In the bottom screen shot R4 and R6 are displaying averages as opposed to sums, so it would seem AVERAGEIFS would be the more appropriate function.
Sorry not to be of more help.