**Question description:**

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

Need help putting together formula where it counts the number of sales but only if the average annual premium is above $360.
So a sales = Sum(N:2:N:48)/360....but not greater than COUNTIF(N:2:N:48) ""

Solved by O. J. in 43 mins

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

Excelchat Expert
26/01/2018 - 03:56

Hello :)

User
26/01/2018 - 03:56

hey

User
26/01/2018 - 03:57

Solving for the yellow box, counting number of life insurance policy sales. But to count as a sale the average premium sold the month must be $360 annual or greater

User
26/01/2018 - 03:57

so someone can't sell 4 $120 annual premium policies and get 4 sale counts

User
26/01/2018 - 03:58

they would bet 1 sale count because total premium is $460. Have to have $360 in annual premium to count

Excelchat Expert
26/01/2018 - 03:58

can you please create

Excelchat Expert
26/01/2018 - 03:58

mplate

Excelchat Expert
26/01/2018 - 03:58

i need to see columns

User
26/01/2018 - 03:58

i pasted one, do you see

Excelchat Expert
26/01/2018 - 03:58

No

User
26/01/2018 - 03:58

colums K-M

User
26/01/2018 - 03:59

down to row 49

Excelchat Expert
26/01/2018 - 03:59

i see now

Excelchat Expert
26/01/2018 - 03:59

ait please

Excelchat Expert
26/01/2018 - 03:59

so please fill as an example

Excelchat Expert
26/01/2018 - 03:59

row 2

Excelchat Expert
26/01/2018 - 03:59

where will be your sales?

User
26/01/2018 - 04:00

thats what we are solving for in yellow box

Excelchat Expert
26/01/2018 - 04:00

so average annual sales are in column E

Excelchat Expert
26/01/2018 - 04:00

yes?

Excelchat Expert
26/01/2018 - 04:00

or in D?

User
26/01/2018 - 04:00

column E is commision amount

User
26/01/2018 - 04:01

D is the amount of annual premium

User
26/01/2018 - 04:01

so in my scenario they should only get 1 sale count

Excelchat Expert
26/01/2018 - 04:01

so then the rows are days?

Excelchat Expert
26/01/2018 - 04:01

why?

User
26/01/2018 - 04:01

there are two sales entries, but average premium per policy is not at or above $360

Excelchat Expert
26/01/2018 - 04:01

because D2 and D3 Are aboce $360?

Excelchat Expert
26/01/2018 - 04:01

above*

User
26/01/2018 - 04:02

rows are life insurance sales

User
26/01/2018 - 04:02

I want someone getting sale count only if average premium is $360 annual per sale

User
26/01/2018 - 04:02

but they can't get more sale counts then applications sold

Excelchat Expert
26/01/2018 - 04:02

just to clarify last time please :)

Excelchat Expert
26/01/2018 - 04:03

if D2/360 is above $360 then count 1

Excelchat Expert
26/01/2018 - 04:03

yes?

User
26/01/2018 - 04:03

yes, but need to account for the other application premiums such as D3

User
26/01/2018 - 04:03

then D4, D5, D6 etc...

User
26/01/2018 - 04:04

as an average ............so average of D2:D49

User
26/01/2018 - 04:04

but sales can't be more than applications

Excelchat Expert
26/01/2018 - 04:05

ah ok

User
26/01/2018 - 04:05

so if someone sells a big $5000 annual premium, they don't end up with a lot of sale counts

User
26/01/2018 - 04:06

but if they only sell small $100 annual premiums they don't get credit on each application, only at $360 annual premium do they get a count

Excelchat Expert
26/01/2018 - 04:06

so if first are 400 and 300 then 3rd is 5000

Excelchat Expert
26/01/2018 - 04:06

output will be 1 for count

Excelchat Expert
26/01/2018 - 04:06

or 3?

User
26/01/2018 - 04:06

no it would be 3

Excelchat Expert
26/01/2018 - 04:06

ok

Excelchat Expert
26/01/2018 - 04:06

let me try now

User
26/01/2018 - 04:07

if first 100 and second 100 and third 200... only 1 count

Excelchat Expert
26/01/2018 - 04:11

why?

Excelchat Expert
26/01/2018 - 04:11

those average is 133

User
26/01/2018 - 04:11

total of 400.

Excelchat Expert
26/01/2018 - 04:11

and this is below 360

User
26/01/2018 - 04:11

get count at 360 annual premium

User
26/01/2018 - 04:11

but no more than the amount of actuall applications

User
26/01/2018 - 04:12

sorry if I confused

Excelchat Expert
26/01/2018 - 04:12

really i m totally confused

Excelchat Expert
26/01/2018 - 04:12

:(

User
26/01/2018 - 04:12

lets start over, im sorry

Excelchat Expert
26/01/2018 - 04:12

ok please

User
26/01/2018 - 04:13

so they get count for every 360 of premium

User
26/01/2018 - 04:13

but no more than the amount of actual applications

User
26/01/2018 - 04:13

when I say "count" I'm referring to "a sale count"

Excelchat Expert
26/01/2018 - 04:13

ok

Excelchat Expert
26/01/2018 - 04:15

please look at column F

Excelchat Expert
26/01/2018 - 04:15

is it correct?

Excelchat Expert
26/01/2018 - 04:15

400 > 360 so the count = 1

Excelchat Expert
26/01/2018 - 04:15

300 < 360 the count = 0

Excelchat Expert
26/01/2018 - 04:15

and etc.

Excelchat Expert
26/01/2018 - 04:15

yes?

User
26/01/2018 - 04:18

yes, sort of. because the sum of column D = 5,761 and there are 4 applications (5,761/4 = 1,440 avg premium per sale).. they would get 4 sale counts

User
26/01/2018 - 04:18

the average premium per sale is 360 or higher

User
26/01/2018 - 04:18

larger sales can lift the average for lower sales

User
26/01/2018 - 04:19

so they get a sale count for every $360 of annual premium but no more than the number of actual applications

Excelchat Expert
26/01/2018 - 04:22

please look

User
26/01/2018 - 04:23

ok

Excelchat Expert
26/01/2018 - 04:23

verage from D2 to D6 is above 360

Excelchat Expert
26/01/2018 - 04:23

he count will be 5

Excelchat Expert
26/01/2018 - 04:23

but the averagefrom D2 to D7 is less 360

Excelchat Expert
26/01/2018 - 04:23

then count remains 5 yes?

User
26/01/2018 - 04:24

thats correct

User
26/01/2018 - 04:25

does it work if all the premiums are $1,000 per year

Excelchat Expert
26/01/2018 - 04:25

yes

User
26/01/2018 - 04:25

to now equal 8 count/sale

User
26/01/2018 - 04:29

so it's basically... Count how many times $360 divides into sum of Annual Premium, but no more than number of appllications

Excelchat Expert
26/01/2018 - 04:30

guess i ve solved it

Excelchat Expert
26/01/2018 - 04:30

pls

User
26/01/2018 - 04:30

they will only enter annual premium if applicaion complete.

Excelchat Expert
26/01/2018 - 04:30

1 min pls

Excelchat Expert
26/01/2018 - 04:31

ready :)

Excelchat Expert
26/01/2018 - 04:31

ase check

Excelchat Expert
26/01/2018 - 04:31

please*

Excelchat Expert
26/01/2018 - 04:31

F and G are working columns dont delete them

Excelchat Expert
26/01/2018 - 04:31

just drag them down

User
26/01/2018 - 04:34

what does just drag down mean

User
26/01/2018 - 04:34

just select those and copy

Excelchat Expert
26/01/2018 - 04:35

i put formulas

Excelchat Expert
26/01/2018 - 04:35

yes

Excelchat Expert
26/01/2018 - 04:35

select the last one and copy down

Excelchat Expert
26/01/2018 - 04:35

i did

Excelchat Expert
26/01/2018 - 04:36

please don't forget to rate our session, Sir

Excelchat Expert
26/01/2018 - 04:36

once it ended

User
26/01/2018 - 04:37

i will for sure

User
26/01/2018 - 04:38

ok thank you!!! looks great!

Excelchat Expert
26/01/2018 - 04:38

thank you to for your patience!

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