Hi All,
I hope everyone is well and safe during this rather odd times.
I'm trying to use drop down menus to cut data to show revenues. I have 2 drop down menus in the "Selection page" tab to show revenues by services (Tax, Audit, etc). The default position in both dropdown is "International Markets" also referred to "Inbound", which should bring back the total number of 838m and the criteria to get that revenues figure (col V) is:
fiscal year "2020" (col x), segment "inbound" (col s).
there are some exception where eg China will include all segments, whereas other criterias in the selection page (region or desk) will on look as inbound or International Markets.
below is the formula I've come up with, which is in the "by LoS" tab. Although this formula works, I am not getting the above total of 838m.
=IFERROR(IFS('Selection page'!$B$9="London Total",SUMIFS('- Data'!$V:$V,'- Data'!$AC:$AC,"Y",'- Data'!H:H,"2020",'- Data'!$S:$S,"Inbound",'- Data'!$L:$L,'by LoS'!$B3),'Selection page'!$G$9="China",SUMIFS('- Data'!$V:$V,'- Data'!$AI:$AI,"China",'- Data'!$X:$X,"2020",'- Data'!$L:$L,'by LoS'!$B6)),SUMIFS('- Data'!$V:$V,'- Data'!$AA:$AA,IF('Selection page'!$B$9="International Markets","*",'Selection page'!$B$9),'- Data'!$X:$X,"2020",'- Data'!$AI:$AI,IF('Selection page'!$G$9="International Markets","*",'Selection page'!$G$9),'- Data'!$L:$L,'by LoS'!$B6))
Unfortunately, i'm unable to add the sample due to it's size. I wanted add the full data without cutting it down to size for upload so that we can reconcile the figure mentioned above.
I have shared a link to the file I've uploaded online
https://digitalpigeon.com/msg/PixacI...eqFfQbIWq2rQQ# - sample.xlsx - sample.xlsx
Please can someone help with this issue.
Any help is greatly appreciated
Bookmarks