# Excel - How to COUNTIF Cell is Greater Than / Less Than Some Value - Expert Solution

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
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
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
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
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
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
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
Excelchat Expert 26/01/2018 - 04:31
ase check
Excelchat Expert 26/01/2018 - 04:31
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.

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.