Excel - SUM Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Any rep that has orders over 500 and occurrences at zero for each month, for a 6 month period. I need a sum
Solved by C. E. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 02/02/2018 - 02:07
Welcome to excel got it pro
Excelchat Expert 02/02/2018 - 02:10
Please share the file..
User 02/02/2018 - 02:11
I dont have it yet
Excelchat Expert 02/02/2018 - 02:12
some sample data then? I can't write a formula if I don't have data.
User 02/02/2018 - 02:12
Aug orders 924 Occurences 1, sept orders 1153 occurences 0 oct orders 934 occurences 6
Excelchat Expert 02/02/2018 - 02:13
Please write the sample in the spreadsheet, it would make much easier to understand.
User 02/02/2018 - 02:13
I need any orders over 500 and occurrences that are zero to be counted
Excelchat Expert 02/02/2018 - 02:14
Is it all?
User 02/02/2018 - 02:14
yes
Excelchat Expert 02/02/2018 - 02:14
You said something about months...
User 02/02/2018 - 02:15
Done
Excelchat Expert 02/02/2018 - 02:17
Please keep in mind that I need sample data to as precise as the original. Now, is it sum or count? you have mentioned both.
User 02/02/2018 - 02:17
sum
Excelchat Expert 02/02/2018 - 02:18
sum of occurrence of 0, right?
User 02/02/2018 - 02:18
yes
Excelchat Expert 02/02/2018 - 02:19
next question, sum of what?
User 02/02/2018 - 02:20
sum of any employee who has orders over 500 with zero occurences
User 02/02/2018 - 02:20
There will be a line for each employee
Excelchat Expert 02/02/2018 - 02:21
As I have mentioned, I need sample data to be as precise as the original data. otherwise I can't write a formula.
Excelchat Expert 02/02/2018 - 02:22
Do you understand what I'm saying?
User 02/02/2018 - 02:22
done
Excelchat Expert 02/02/2018 - 02:24
This is not enough. employee is a text, you can't sum text, you need numbers.
Excelchat Expert 02/02/2018 - 02:24
Please understand my situation. If you don't provide enough data I'm helpless.
User 02/02/2018 - 02:24
I gave you numbers
User 02/02/2018 - 02:25
Orders and occurences are both numbers
Excelchat Expert 02/02/2018 - 02:25
but you said sum employee.
User 02/02/2018 - 02:26
sum of all occurrences that are zero for the months shown
Excelchat Expert 02/02/2018 - 02:26
is it that you want to sum orders for each employee?
User 02/02/2018 - 02:26
NO
Excelchat Expert 02/02/2018 - 02:27
please show example of result. that way it might be easier.
User 02/02/2018 - 02:27
I need a sum of all occurences that are zero for orders that are great er than 500
Excelchat Expert 02/02/2018 - 02:27
please show example of result of sample data set you gave. that way it might be easier.
User 02/02/2018 - 02:27
The total right now would be 1
User 02/02/2018 - 02:28
only one months show orders greater than zero with no occurences
User 02/02/2018 - 02:28
orders great er than 500, sorry
Excelchat Expert 02/02/2018 - 02:28
then it is not sum, this is called count.
User 02/02/2018 - 02:28
ok
Excelchat Expert 02/02/2018 - 02:28
now I fully understand the problem.
User 02/02/2018 - 02:29
kk good thanks, sorry for the confusion
Excelchat Expert 02/02/2018 - 02:29
Thanks for being patient and making it clear. gimme a few minutes to write the formula.
Excelchat Expert 02/02/2018 - 02:46
Okay, here is a possible solution. Based on the data representation, first you need to cluster every order and occurrence and perform a AND operation as showed in G4, G5, G6. Then use a COUNTIF formula like showed in E5.
User 02/02/2018 - 02:48
Thank you
Excelchat Expert 02/02/2018 - 02:49
Glad to be able to help.
Excelchat Expert 02/02/2018 - 02:49
Please close the session and leave a feedback for me.
Excelchat Expert 02/02/2018 - 02:49
Have a great day.

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.