I need a formula in Google Sheets that does the following

1

I need a formula that:

  • Counts the number of cells that contain either 1 or .
  • Returns different numbers based on that count

The pattern is: the sum of 

1 = 350 
2 = 400 
3 = 450 
4 = 500 
5 = 600 
6 = 650 
7 = 750

It is a formula to count how much money an instructor gets for teaching one lesson. The amount depends on the number of students in class, where 1 means that the student was in class and the dot means he attended it online. The pay is the same in either case. Note that the amount is not linear.

For example:

  • If there are two . and two 1 return the value 500.
  • If there are two . and three 1 return the value 600.

Example sheet:

example

Dimitri Stukalov

Posted 2016-12-06T00:17:41.573

Reputation: 11

Question was closed 2016-12-06T09:38:41.907

Have you tried anything so far? Will you be counting these values in specific cells, rows, columns, or ranges? – music2myear – 2016-12-06T00:19:21.007

Is there a pattern to what value the sum should correspond to? – timotree – 2016-12-06T00:50:52.183

@timotree , yes the pattern is: the sum of 1 gives 350 2 = 400 3 = 450 4 = 500 5 = 600 6 = 650 7 = 750 it is a formula to count how much money an instructor gets for teaching one lesson. The amount depends on the number of students in class, where 1 means that the student was in class and the dot means he attended it online. – Dimitri Stukalov – 2016-12-06T01:32:38.413

@fixer1234 I have an answer. Should I not post it if this is going to get migrated? – timotree – 2016-12-06T02:10:50.003

1Google Spreadsheet questions are on-topic at the Web Apps sister site but off-topic here. – fixer1234 – 2016-12-06T02:35:00.493

What is the pattern after 7, or is that a maximum student count? – fixer1234 – 2016-12-06T02:50:51.447

Answers

1

Put this formula in the top spot for each column (range references shown for column A):

=CHOOSE(SUM(COUNTIF(A$5:A, "=."), COUNTIF(A$5:A, "=1")) + 1, 0, 350,400,450, 500, 600, 650, 750)

CHOOSE selects the value from the list based on the SUM. If the list expands, just add values to the list for counts over 7.

Here is a spreadsheet with that if you want to play around with it.

timotree

Posted 2016-12-06T00:17:41.573

Reputation: 742

Thank you very much for your help, the formula does the job! Is there any way to improve it for those days that haven’t been counted yet, so that there would be no error warning #NUM! when some cells are still blank? for instance tinypic.com/r/23vgjr4/9 – Dimitri Stukalov – Dimitri Stukalov – 2016-12-07T23:33:04.823

@DimitriStukalov I've changed it. Now if the count is 0 it will add 1 and get the first item from the list (which is 0). – timotree – 2016-12-08T00:29:31.967

Asked: 2016-12-06T00:17:41.573

Viewed: 79 times

Active: 2016-12-08T00:28:45.150