# Excel - IF Function Problem - Expert Solution

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
User 26/01/2018 - 02:53
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
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
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
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
Excelchat Expert 26/01/2018 - 03:33
or commission
User 26/01/2018 - 03:33
yes
User 26/01/2018 - 03:33
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
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.

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.