Array help and counting

  • Thread starter Thread starter Pablo
  • Start date Start date
P

Pablo

I have an array that I am counting to do a count on
positive values within the same dimension (row) going
forward from its initial point. The reference value is a
date value.

May Jun Jul Aug Sep Oct Nov Dec
87 54 43 31 18 (2) (13) (23)

If I took the current month I want to return the count of
positive values starting with Jun, so the result would be
4. If we were in the month of Aug the result would be 2.

My brain is hurting on this.

Thanks,
 
Assuming the sample data is in A1:H2
May Jun Jul Aug Sep Oct Nov Dec
87 54 43 31 18 (2) (13) (23)
Click to expand...

with A1:H1 containing dates custom-formatted in "mmm"

Try say, in A4:

=SUMPRODUCT(($A$1:$H$1>=A1)*($A$2:$H$2>0))

Copy A4 across to H4

The above will return the desired counts
 
Max, thanks for responding. I did not fully explain
myself but I was able to apply it, but I need a little
more help.

A1 - contains date ie... 6/01/04 custom 'mmm'

B3:I5
May Jun Jul Aug Sep Oct Nov Dec
87 54 43 31 18 (2) (13) (23)
23 34 90 (43) (54) (71) (83) (98)

A4 = 4
A5 = 2

If A1 changes to July, A4 & A4 change accordingly

Make sense?
 
This may better suit your sample lay-out as described ..

Put in say, B20:

=SUMPRODUCT(($B$3:$I$3>=B$3)*($B4:$I4>0))

Copy B20 across to I20, then copy down to I21

The above will return the count results for B4:I4 and B5:I5
aligned directly below the months' cols (May to Dec) in B3:I3

We're going to use B3:I3 to look-up the column corresponding
to the particular month in A1

Put in A4:

=OFFSET($B$3,ROW(3:3)+14,MATCH($A$1,$B$3:$I$3,0)-1)

Copy A4 down to A5

If A1 = Jun, A4 and A5 will return 4 and 2 respectively
If A1 = Jul, A4 and A5 will return 3 and 1 respectively

Note that the B3:I3 must be dates, i.e. 1/05/04, 1/06/04, etc
formatted in "mmm" which will match with the date in A1
-----

For A4, "ROW(3:3)+14" evaluates to 17 which is the number
of intervening rows between row 3 and row20 - the first row of
the corresponding count results

Just adjust the number "14" so that the total in "ROW(3:3)+14"
is equal to the number of intervening rows between row 3 and
the first row in which you're placing the corresponding count results
(in case you need more intervening rows than 17)
 
Hi,

Assuming that the dates in Row 3 are real dates custom formatted as
"mmm", try entering the following formula in Cell B4 and copy it down as
far as you need to:

=SUMPRODUCT(--($B$3:$I$3>=$A$1),--(B4:I4>0))

Hope this helps!
 
Max and Domenic - Thanks to both of you.

Domenic this is exactly what I needed. Can you please
explain what is happening with it?

Pablo
 
Pablo,

According to the Help menu, "Sumproduct multiplies the corresponding
components in given arrays, and returns the sum of those products".

Here, the arrays for your first formula are B3:I3 and B4:I4. This is
whats happening:

Logical Expression Result Numerical Equivalent
---------------------- -------- ------------------

B3>=A1 False 0
B4>0 True 1

0*1=0

C3>=A1 True 1
C4>0 True 1

1*1=1

This continues until it reaches the end of the arrays, and then it adds
the products to get a result of 4.

Notice that the double dashes "--" in front of the logical expression
converts a TRUE or FALSE result into their numerical equivalents of 1
and 0, respectively.

Hope this helps!
 
Back
Top