Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to add Month YTD using Getpivotdata in Google sheet

0

Hi All,

Hope you're enjoying the lovely weather!

I'm using google sheet where I understand, it can't give me ytd total, so am trying to add April and May cost/income so I have the YTD totals but the formular built by someone isn't working for me.

IFERROR(GETPIVOTDATA($AI$7,$V$7,$V$7,$B14),"0")-Current formular

FERROR(GETPIVOTDATA($W$7,$V$7,$V$7,$B6),"0")-I've replaced AI7 with W7 (For April) and I can see it picking April amount which is correct

$W$7,-this is the April sum column from the pivot table-x is May and Y is June etc

This is the current pivot table columns

$V$7-is the column header with the list of products

$V$7-is the column header with the list of products- again

The cell column is outside the pivot table where is value/answer should be

$B6-is the column where the income /value should report

FERROR(GETPIVOTDATA($W$7,$V$7,$V$7,$B6),"0")-how can I tweak/extend this formular to add May income so I have the total income for April and May

iferror((getpivotdata($AZ$7,$AM$7,$AM$7,$B15))-$AZ$7,this is the total costs of full year cost

$AM$7-is the column header with the list of products

$AM$7-is the column header with the list of products- again

$B15-is the column where the costs /value should report

how can I tweak/extend this formular to add May income so I have the total income for April and May and the following months of the financial year.

i'll deepy appreciate a swift response.

Answer
Discuss

Discussion

Hi John,

Thanks for picking up my query- we've made a good start. The ytd for each month should be for each product as you will imagine am working on April and May now because the business combine April & May as ytd, so from the model am working with been taken over my someone, I'm meant to sum up April and May across the products to arrived at the ytd figures for  income and costs.
siehartley2001 (rep: 10) Jun 18, '22 at 1:17 pm
Add to Discussion

Answers

0
Selected Answer

Hi Sie (Isaac)

You can get that effect by using SUM and GETPIVOTDATA within an array formula (which in versions before 365, you would need to enter using Ctrl+Shift+Enter).

In the attached file, I modified your upper pivot table (only) by changing the Custom Field names (so e.g. it shows "April" rather than "SUM of Sum of April" as a column heading) and the £ sums to "Month value" so it makes a bit more sense on first glance.

Having done that, in the "Data" worksheet cell B6 I put this array formula:

=SUM(GETPIVOTDATA(""&$R3:R3,$Q$3,"Month Value",$Q$4:$Q$6))

This gives the sum of April to April (so just April!) but can then be copied right (or filled) so C6 is April to May, D6 is to June etc.

It works like this:

  1. The SUM is wrapped around the GETPIVOTDATA formula which extracts several "Month Value" figures (£)- the third argument in the GETPIVOTDATA part above.
  2. The second argument of GETPIVOTDATA specifies the table the data will come from (i.e that involving cell Q3, upper left in your pivot).
  3. The last argument $Q$4:$Q$6 fixes the rows for which the values in your pivot table are to be returned (i.e. Product 1, Product 2 and Product 3) and summed.
  4. The first argument ""&$R3:R3 sets the columns for which row data will be returned. $R3 fixes the leftmost header and is followed by R3 (with no $) which will increment as the formula is copied right (so becomes ""&$R3:S3 which equates to the columns headed April and May etc.). For reasons known best to Microsoft, you have to append a blank string (using ""&) to get this method of addressing to work within GETPIVOTDATA.

The nett result is that it recalculates the Grand Totals in your pivot table and you have a YTD (year to data) array formula!

REVISION:

The second attached file below also address the issue of YTD costs and income data per product.

In yellow-shaded cell A16 (of worksheet "Data"), list data validation provides a dropdown from which you can pick a month (from R3:AC3 of my revised pivot table), e.g. June.

Below that, in A17, the MATCH formula below determines which cell the chosen month is:

=ADDRESS(3,17+MATCH(A16,R3:AC3,0))

It uses MATCH with a third argument of 0 (for an Exact Match) but if you have Excel 365, you can use XMATCH instead (where that's the default). You could move that cell elsewhere (or set the font colour to match the background (so the user doesn't see it.

Then, for Cost data, a typical formula (in B18) is:

=SUM(GETPIVOTDATA(""&$R3:(INDIRECT(A17)),$Q$3,"Month Value","Product 1"))

where the second part of $R3:R3 (used above for YTD) is replaced by the INDIRECT formula which points to what A17 contains. Change A16 to read June, and it will point to T3 (which is in A17 for that choice).

The income formula is a bit different, e.g. in C18 we have:

=SUM(GETPIVOTDATA(""&$R10:OFFSET((INDIRECT(A17)),7,0),$Q$10,"Month","Product 1"))

since it looks in a different table but the wrap-around of OFFSET points to 7 rows below that in A17. That means as you change A16, the income formulae point to the right month from their pivot table.

Finally,D18:D21 calculate the percentage YTD profit margin (note it is margin, not mark-up).

Hope this works for you.

Discuss

Discussion

p.s. please enter your Excel version in your Forum Profile- it could affect the answers which work can be probided (and work for you).

It's a bit overcast here in the UK today but was glorious yesterday- I was outside with chatting and drinking with friends until midnight :)
John_Ru (rep: 3992) Jun 18, '22 at 5:08 am
Hi John,

Thanks for picking up my query- we've made a good start. The ytd for each month should be for each product as you will imagine am working on April and May now because the business combine April & May as ytd, so from the model am working with been taken over my someone, I'm meant to sum up April and May across the products to arrived at the ytd figures for  income and costs.
siehartley2001 (rep: 10) Jun 18, '22 at 1:17 pm
Hi John,
Hope you had a good weekend. I've added a requirement on the forum and was hoping you could possible review it for me. Thanks:)
siehartley2001 (rep: 10) Jun 20, '22 at 4:53 am
Sie

Please note that this is a Q&A Forum- you ask a straightforward and clear question and (hopefully) someone replies. You're not meant to specify a bunch of detailed requirements and expect us to provide a "mini-project" solutuion.

That said, I'll modify my answer to show how you might get YTD data per product but it will be on the basis of the file I provided for the YTD data- you'll have to put data in the right place if needed.
John_Ru (rep: 3992) Jun 20, '22 at 9:14 am
 Sie   I think I gave you enough clues on how to do whar you want and don't have more time to spare on your problem, sorry.   I've deleted my answer to your latest question, so someone else might reply.
John_Ru (rep: 3992) Jun 20, '22 at 10:18 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login