Sumif with 2 conditions, one being 'fetch data from last Col' from a Dynamic Pivot table

[SekharS]

Hi Experts,

I am trying to do a Dashboard, where the source data is realtime from server, configurable as a pivot table in 'Data' tab.

I configured a pivot table, which looks like the one in 'Data' tab.
FYI - in attached, I just copy pasted values there, to re-create the data/format.

What I wanted to accomplish is:
1. In 'Summary' tab
2. sum up the Pivot Table rows that contain keywords "Modernization" & "Architecture" seperately.
3. Create a pie chart
4. Make it display the current numbers & Pie chart, upon every refresh of Pivot table.

I was successful in #2 & #3, but for #4:
1. I need a formula in 'Summary' tab cell D5 & D6, where in
2. It sums up the "last" Col of 'Data' tab, based on Keyword 'Modernization' or 'Architecture'.
3. FYI : Each row in in Pivot will contain only one of the keywords. Either 'Modernization' or 'Architecture'.

The formula that I came up with can reference to a static col (eg, G in this case).
But I need something that references to the last col, no matter how pivot table COl increase, as months go by..

Hope I was able to explain it clearly.
Thank you in advance.

Best Regs
Sekhar

 

[Chihiro]

Try this formula.
=SUMIF(Data!B:B,"*"&C5&"*",OFFSET(Data!A:A,,SUMPRODUCT(MAX((Data!$5:$5<>"")*COLUMN(Data!$5:$5)))-1))

 

[John Jairo V]

Hi!

Another option, without volatile functions:
=SUMIF(Data!B:B,"*"&C5&"*",INDEX(Data!$1:$1048576,,MATCH(9E+307,Data!5:5)))

Blessings!

 

[bosco_yip]

Or..........

=SUMIF(Data!B:B,"*"&C5&"*",INDEX(Data!$1:$1,MATCH("zzz",Data!$3:$3)))

Regards
Bosco

 

[John Jairo V]

@bosco_yip: if you use a different sizes of ranges in SUMIF, the formula becomes volatile. Blessings!

 

[bosco_yip]

@bosco_yip: if you use a different sizes of ranges in SUMIF, the formula becomes volatile. Blessings!

Hi John,

Thanks for your advice

Regards
Bosco

 

[SekharS]

Thanks to you all Experts: Chihiro, John Jairo V & bocco_yip.
Really appreciate your quick responses & help.

Also, if you can suggest:
What's the best way to learn to construct such formulas & also step into VBA & Macros?

Thanks in advance
Sekhar

 

[Khalid NGO]

What's the best way to learn to construct such formulas & also step into VBA & Macros?

Hi,
You are at the right place, believe me there are many people (including me) who was not aware of basic functions before joining this forum.

You just need to focus on provided solutions, try Evaluate option for the formula and understand how the function is working. Once you realize how formulas are working, try to attempt the answer for other questions posted here. This way you will surely learn quickly.

Don't worry about the wrong attempts or mistake. Remember this famous saying:
"The person who never made a mistake never tried anything new"

Keep Exceling,
Regards,

 

[Marc L]

Hi !

[…] & also step into VBA & Macros?

Activate Macro recorder and operate manually :
you will get a free base code !
For each statement in the generated code, read VBA inner help.

See also codes in the appropriate VBA Macros section …
​

 

[Chihiro]

Search tool in the forum and Google Search (advanced search feature especially) is your friend

I've learned most of what I know from tackling various problems posted here and in other forums. As well as finding sample solutions and workbooks, then breaking it apart and reconstructing it from base data.

Use F9, Evaluate Formula, Helper columns etc to break down formula into component parts and understand how each part works.

There are many samples (especially dashboards) in https://chandoo.org/ blog posts that you can play with.

 

[GraH - Guido]

Khalid NGO, Marc L, Chihiro, that's all priceless advice.

 

[SekharS]

Thank you Khalid NGO, Marc L & Chihiro.
Grah - Guido, rightly said, priceless advice.

Will start my baby steps sooner than later