Question description:
This user has given permission to use the problem statement for this
blog.
amending a formula so that there is another criteria and if it does not meet this criteria then we stick with the original criteria
Solved by A. B. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
21/08/2018 - 09:17
Hello! How are you?
User
21/08/2018 - 09:18
Hello im good thank you
User
21/08/2018 - 09:18
thank you for taking my query
User
21/08/2018 - 09:18
https://docs.google.com/spreadsheets/d/1gPqx5iuwoaA4W4z1-Y8rsi9E5yYGmW68zuEaxa_Z5XE/edit#gid=9786050
User
21/08/2018 - 09:18
this is the google doc i need help in working on
Excelchat Expert
21/08/2018 - 09:18
That's good to hear. You're welcome. Is it possible to share with me the original formula and what you wish to change?
Excelchat Expert
21/08/2018 - 09:18
Okay, opening it now
Excelchat Expert
21/08/2018 - 09:19
It looks like it is not open for general access
User
21/08/2018 - 09:19
ive given you access
Excelchat Expert
21/08/2018 - 09:20
Alright. Thanks. Which tab should I be looking at?
User
21/08/2018 - 09:20
the main sheets tab
User
21/08/2018 - 09:20
if you stay on the main working sheet at AF2:AI7 we need to work the quarter into the formulas we have here
so when the Financial quarter (which will be given in AH4) is given it needs to be an extra critera in AI5, AI6 and AI7 along with the current criteria
but if it is not given then then excluded and we are as we are and the current formula is correct
the current formula in AI5 is to work out the Sales which is a total of column S, AI6 to work out the total commission in column AE and AI7 is to work out the total claw back in column AF
using the criteria of who the sales person is in column E and the currency of the invoice in Column O
what i would like to do going forwards no that i have the added info of the financial quarter and year is be able to enter that in AH4 and it will work out the name of the sales person/currency of the invoice and Financial quarter and total all the invoices (either as total sales/commision or clawback depending on the row) after meeting these criteria
Excelchat Expert
21/08/2018 - 09:21
Okay please give me a few minutes to digest this new information
User
21/08/2018 - 09:21
thank you
Excelchat Expert
21/08/2018 - 09:26
So basically, you would like to create a SUMIFS which considers the Sales Person, Currency and Financial Quarter - is this correct?
User
21/08/2018 - 09:26
no
Excelchat Expert
21/08/2018 - 09:26
As of now the SUMIF formula in column AI only accounts for the Sales Person
User
21/08/2018 - 09:27
i would like the formula that is currently there to also consider the Financial Quarter in column A
User
21/08/2018 - 09:27
so as it stand for example AI4
Excelchat Expert
21/08/2018 - 09:27
Okay, that's doable. Do we ignore the Local Currency?
User
21/08/2018 - 09:27
would only include sales for constance
User
21/08/2018 - 09:27
yes we ignore it
User
21/08/2018 - 09:28
but i would like it to include sales for constance in Q1 FY18 for example
User
21/08/2018 - 09:28
and i would put Q1 FY18 in AH4
Excelchat Expert
21/08/2018 - 09:28
Got it. Let me write the formula for you
User
21/08/2018 - 09:28
so as it stand for example AI5*
Excelchat Expert
21/08/2018 - 09:31
Hi, I have now modified the formula to include the Financial Quarter criteria in in cells AI5 to AI7
User
21/08/2018 - 09:31
but if there is nothing in the Financial quarter then i want the formula to ignore the Finaicial quarter column
User
21/08/2018 - 09:31
i forgot to mention - my mistake
Excelchat Expert
21/08/2018 - 09:31
I see. Give me a minute. That's just an easy fix :)
User
21/08/2018 - 09:32
thank you
Excelchat Expert
21/08/2018 - 09:33
Hi! I am done
Excelchat Expert
21/08/2018 - 09:34
Could you please check?
User
21/08/2018 - 09:34
perfect let me check
User
21/08/2018 - 09:37
perfect thank you it works!
Excelchat Expert
21/08/2018 - 09:38
Cool. Happy to hear that.
Excelchat Expert
21/08/2018 - 09:38
Would you like me to explain the solution to you?
User
21/08/2018 - 09:38
no thank you i trust it
User
21/08/2018 - 09:38
thanks for your help!
Excelchat Expert
21/08/2018 - 09:38
You're welcome.
Excelchat Expert
21/08/2018 - 09:39
If that is the case, then you may end the session any time now by clicking the END SESSION button on the upper right corner of the screen.
Excelchat Expert
21/08/2018 - 09:39
Appreciate if you could leave a good feedback on a survey afterwards
Excelchat Expert
21/08/2018 - 09:39
Thanks and have a great day ahead!
Excelchat Expert
21/08/2018 - 09:40
Hi? Are you still with me?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
