Excel - IF Function Problem - Expert Solution

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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.