Count cells in Excel

Question

I have a specific and difficult task that I will explain further. I won't use VBAExcel since this is not the case:

• I have 5 columns in a specific row like this: 50 100 blank blank blank - parameter A.
• In the same row I have other 5 columns like this: 50 blank 100 blank 100 - parameter B

Those numbers correspond to marks in different evaluations. In the first set there are 2 numbers and in the second 3 numbers. But the total number of evaluations are 4. How can I count them? Each evaluation contains "A" and "B" or only one of them because the total is 100. Only the 4th evaluation doesn't exist. I want to count the total number of evaluations for every situation.

• 1st test: A - 50 B = 50;
• 2st test: A - 100 B = blank;
• 3st test: A - blank B = 100;
• 4st test: A - blank B =blank; - this test didn't happen.
• 5st test: A - blank B = 100;

tests = 5

Answer 1

You can use following formula.

=SUMPRODUCT(--((A6:E6+F6:J6)>0))

Adjust the ranges to suit actual columns

Answer 2

Not sure this is the best way but here are the steps I take:

• First, i used offset to split range contains number into 2 parallel arrays with different color (yellow and blue)
• Next, combining them together to identify the vacant positions simultaneously. Evaluating these sum of pairs> 0 ->> returns the result = 1, and the
• Finally, calculate the number of pairs.

.

D8=SUMPRODUCT(--((OFFSET(A6:J6,,,1,5)+OFFSET(A6:J6,,5,1,5))>0))

Hope it works!

Answer 3

As far as I understood your requirement you can use a helper row with some random indexes. Thus, even the 4th test where the result is empty will be counted and your number of evaluations will be 5.