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