**Question description:**

*This user has given permission to use the problem statement for this blog.*

Need a formula that counts (1,2,3,4 ect..in cell M21) a range of cells, N4:N20, which are annual premium values. But I only want to add a sale count if the average monthly premium is above $30 per sale. So if someone has 4 sales but they don't equal a total of $120/mth ($30 x 4 = $120)... they would only get a count of 3 sales.

Solved by S. E. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
26/01/2018 - 02:49

Thanks for choosing Got It Pro

Excelchat Expert
26/01/2018 - 02:50

do you have a file with data

User
26/01/2018 - 02:51

i don't that I can provide

Excelchat Expert
26/01/2018 - 02:51

ok

User
26/01/2018 - 02:51

right now my formula is using COUNTIF if there are any values in the annual premium

User
26/01/2018 - 02:51

so they only enter annual premium if sold and issued

User
26/01/2018 - 02:52

I need a quality assurance factor, where the sales have to average over $30/mth to count

Excelchat Expert
26/01/2018 - 02:53

what does N4:N20 contains

Excelchat Expert
26/01/2018 - 02:53

the premium in amount?

User
26/01/2018 - 02:53

Annual premium amounts

Excelchat Expert
26/01/2018 - 02:53

ok

Excelchat Expert
26/01/2018 - 02:53

so if the premium is below $30/month

Excelchat Expert
26/01/2018 - 02:54

then it should not be counted

User
26/01/2018 - 02:54

correct

User
26/01/2018 - 02:54

or $360 annual

User
26/01/2018 - 02:54

correct

Excelchat Expert
26/01/2018 - 02:54

ok so annually $360

Excelchat Expert
26/01/2018 - 02:54

ok

User
26/01/2018 - 02:55

yeah so if there are 4 sales but they don't add up under $360x12 then the count is 3

User
26/01/2018 - 02:55

i guess i should have left out word "don't" above

Excelchat Expert
26/01/2018 - 02:55

so can I assume that if the premium is less than $360 then the count should not be considered

User
26/01/2018 - 02:56

on average, yes

User
26/01/2018 - 02:56

but if someone has a sale of 300 and 3500, then they should get a count of 2

Excelchat Expert
26/01/2018 - 02:56

so for example in column N if we have 300, 400, 500 & 600

Excelchat Expert
26/01/2018 - 02:56

then it shuold count 3

User
26/01/2018 - 02:56

not less because average is above 360 per policy

User
26/01/2018 - 02:57

but not more because they had a big sale

Excelchat Expert
26/01/2018 - 02:57

so you mean the average

Excelchat Expert
26/01/2018 - 02:57

okay

Excelchat Expert
26/01/2018 - 02:57

so the annual premium is in N4:N20, right

User
26/01/2018 - 02:57

your example would be 4 sales

Excelchat Expert
26/01/2018 - 02:58

you have to give me the exact number of rows

Excelchat Expert
26/01/2018 - 02:58

in which the anual premium are there

Excelchat Expert
26/01/2018 - 02:58

then only we can average, right

User
26/01/2018 - 02:58

the sales all count as 1 sale only, as long as the average premium per sale is at least $360

Excelchat Expert
26/01/2018 - 02:59

I got it

Excelchat Expert
26/01/2018 - 02:59

so i need the exact number of rows

User
26/01/2018 - 02:59

exact is N4:N50

User
26/01/2018 - 02:59

46 rows

Excelchat Expert
26/01/2018 - 03:00

ok

Excelchat Expert
26/01/2018 - 03:02

working on it

Excelchat Expert
26/01/2018 - 03:03

can in the annual premium, some rows could be blank also

User
26/01/2018 - 03:03

yes

Excelchat Expert
26/01/2018 - 03:04

ok

User
26/01/2018 - 03:04

they might have a sale not issue so leave annual premium cell blank but have one underneath or above that issues and would have annual premium

Excelchat Expert
26/01/2018 - 03:08

so it should only count if it is above average

Excelchat Expert
26/01/2018 - 03:08

not equal to and above, right

User
26/01/2018 - 03:08

equal to and above

User
26/01/2018 - 03:08

so 360 is good

Excelchat Expert
26/01/2018 - 03:08

ok

User
26/01/2018 - 03:08

so >359 good

Excelchat Expert
26/01/2018 - 03:09

let me change the formula

Excelchat Expert
26/01/2018 - 03:10

uploading the file

[Uploaded an Excel file]

Excelchat Expert
26/01/2018 - 03:10

please check if this is what you are looking for

User
26/01/2018 - 03:12

it should be 3 sales because there are 3 sales and the average annual premium is 400

Excelchat Expert
26/01/2018 - 03:12

you mean all are above 360, right

User
26/01/2018 - 03:13

the sales count if average premium checks out to 360 or higher

Excelchat Expert
26/01/2018 - 03:14

ok got it

Excelchat Expert
26/01/2018 - 03:14

let me tweak the formula

User
26/01/2018 - 03:14

so a sale under 360 can still count if the other sales are higher and bring average up

Excelchat Expert
26/01/2018 - 03:16

do you want the count when the average is premium is above the total average

Excelchat Expert
26/01/2018 - 03:16

or when it is above 360

Excelchat Expert
26/01/2018 - 03:16

the first formula does that

Excelchat Expert
26/01/2018 - 03:17

so 2 were greater than the average of the 3

Excelchat Expert
26/01/2018 - 03:17

so it counted 2

Excelchat Expert
26/01/2018 - 03:18

if you want I can change it to count all that are above 360

Excelchat Expert
26/01/2018 - 03:18

or do you want to count if average is greater than 360

User
26/01/2018 - 03:19

Its the average and the sale amount

User
26/01/2018 - 03:19

because I don't want a huge sale of $4000 annual and one that is $200 annual and they get more than 2 sale counts

Excelchat Expert
26/01/2018 - 03:19

is sale amount same as premium

User
26/01/2018 - 03:20

it would only be 2 sales in that scenario

User
26/01/2018 - 03:20

can't get more sale amounts than actual sales

Excelchat Expert
26/01/2018 - 03:20

so in this case they should get 1 count?

User
26/01/2018 - 03:20

but if you have 4 sales at $100 annual, you only get credit for 1 sale

Excelchat Expert
26/01/2018 - 03:21

I am sorry but you will have to define the criteria more clearly

User
26/01/2018 - 03:21

sale is just a submitted application, so different than premium, but I don't consider a sale a sale if not worth at least 360 annual

Excelchat Expert
26/01/2018 - 03:21

for me to come up with the correct formula

Excelchat Expert
26/01/2018 - 03:22

so sale is a separate field?

User
26/01/2018 - 03:22

can you paste what you have so far... i might be able to run with it

Excelchat Expert
26/01/2018 - 03:22

can you upload a small sample of your data

User
26/01/2018 - 03:23

yeah hangon

User
26/01/2018 - 03:27

can you see what I just pasted

Excelchat Expert
26/01/2018 - 03:27

yes

User
26/01/2018 - 03:27

the yellow box at the bottom to the left is what we are solving for

User
26/01/2018 - 03:28

ignore the colum with the #ref and the yellow box at bottom right

Excelchat Expert
26/01/2018 - 03:29

where is the sale and premium amount

User
26/01/2018 - 03:29

sale amount is the bottom left box

User
26/01/2018 - 03:29

premium amount is blank at this time but there is annual premium header

User
26/01/2018 - 03:30

annual premiums would go in there. right now the sales are just counted if anything is in commission column

Excelchat Expert
26/01/2018 - 03:30

sale amount is in which cell

User
26/01/2018 - 03:30

A50

Excelchat Expert
26/01/2018 - 03:30

ok

User
26/01/2018 - 03:30

I assumed we need to move to count the annual premium column instead of commission column

Excelchat Expert
26/01/2018 - 03:31

yes I believe you wanted the count of the premium

Excelchat Expert
26/01/2018 - 03:32

what average should i take

Excelchat Expert
26/01/2018 - 03:33

the premium, right

Excelchat Expert
26/01/2018 - 03:33

or commission

User
26/01/2018 - 03:33

yes

User
26/01/2018 - 03:33

premium

User
26/01/2018 - 03:33

not commission

Excelchat Expert
26/01/2018 - 03:34

is the condition correct in the formula

User
26/01/2018 - 03:35

no it would be 4 sales

Excelchat Expert
26/01/2018 - 03:35

what is there in column A

Excelchat Expert
26/01/2018 - 03:36

how?

Excelchat Expert
26/01/2018 - 03:37

you give me some numbers to enter in col D

User
26/01/2018 - 03:37

there are 4 sales, and the average premium is $450 per sale

Excelchat Expert
26/01/2018 - 03:37

so it would be 2 right

User
26/01/2018 - 03:37

when you add up the 4 annual premiums and / 4 you get 450

Excelchat Expert
26/01/2018 - 03:38

300 & 400 are below the average, right

User
26/01/2018 - 03:38

no they get the sale count....AS LONG as they average premium's check out to 360 on average

Excelchat Expert
26/01/2018 - 03:38

so shall I change the average to 360 in formula

User
26/01/2018 - 03:39

yeah but the average premium amount is used to validate the sale amounts as good

User
26/01/2018 - 03:39

yeah 360 needs to be in there

Excelchat Expert
26/01/2018 - 03:39

is commission same as sale

User
26/01/2018 - 03:41

its almost like the formula needs to count the how many times total annual premium/360 but not more than countif:(N3:N49)

Excelchat Expert
26/01/2018 - 03:42

sorry, did not get what you are saying

User
26/01/2018 - 03:42

=SUM(N:4:N:49)/360 but not more than COUNTIF(N:4:N49)

User
26/01/2018 - 03:43

something like that

Excelchat Expert
26/01/2018 - 03:43

ok let me try

Excelchat Expert
26/01/2018 - 03:44

what should be the criteria for counting?

User
26/01/2018 - 03:45

something in the cell N:4:N:49 other than $0.00

Excelchat Expert
26/01/2018 - 03:45

which number should come there

User
26/01/2018 - 03:47

a count (1,2,34 etc..) on how many cells have value different than $0.00

User
26/01/2018 - 03:48

but count is reduced if the average annual premium is less than $360 per policy.

User
26/01/2018 - 03:49

formula takes the lower of values is N:4-N:49 and how many times 360 goes into the sum of N:4-N:49

Excelchat Expert
26/01/2018 - 03:49

please create a new session

Excelchat Expert
26/01/2018 - 03:49

he time is up

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