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.
