Compare array to other array, row by row

[cacos]

Hi forum!

Let's see if I can explain this one clearly: I need to compare an array of multiple columns and rows, to another of 1 column and equal number of rows.

The comparison needs to be row by row, for example as you'll find in the attached example, compare all values in C3:E3 to F3, and then move to the next row. The results needs to be reflected in a single cell for the whole operation, for example H2 in the attached file.

Basically we need to check how many countries reached their goal in Jan-March period, their goal being in column F. The result would be a single number, the amount of instances where the goal was met.

Thank you!

 

[tomas]

See if this works for you

 

[cacos]

It does!

But do you know how I can refer to the whole array rather than doing one column at a time? I will be more columns in the actual file.

EDIT: I've just adjusted the first range to the whole 3 columns and it worked, nevermind! QUESTION: why the "/1"?

THANK YOU!

 

[tomas]

=SUMPRODUCT((C3:E11>=F3:F11)/1)

this works as well if you use ctrl,shift,enter

 

[tomas]

It does!

But do you know how I can refer to the whole array rather than doing one column at a time? I will be more columns in the actual file.

EDIT: I've just adjusted the first range to the whole 3 columns and it worked, nevermind! QUESTION: why the "/1"?

THANK YOU!

Ou I haven't read your post to the end.

about /1:

formula uses logical operator greater than something so the result is array of trues and falses. And if you do whatever mathematical operations on this array trues are converted to ones and falses to zeros.

This ones are then summed and hence result.

mathematical operation "/1" is convenient because it converst and as denominator doesn't change the result

 

[cacos]

Got it, thank you!

 

[prasaddn]

Hi,

on a side note, if I need to know how many countries did reach goal atleast once in those 3 months period, how would i acheive?

If a country acheives more than once goal in those three months period, it should be still counted as one country.

Since we have 9 countries listed here, our answer shall never be more than 9.

Regards,
prasad
PS: In fact, I was thinking thats what OP wanted.

 

[prasaddn]

Hi,
Well, for my question above in post #7, I figured out the formula myself:
=SUM(IF(MMULT(IF((C3:E11>=F3:F11),1,0),{1;1;1})>=1,1,0))
enter as CSE.

Regards,
Prasad DN
PS: Incase anyone wanted to know.

 

[Tiago MF]

Hi,
Well, for my question above in post #7, I figured out the formula myself:
=SUM(IF(MMULT(IF((C3:E11>=F3:F11),1,0),{1;1;1})>=1,1,0))
enter as CSE.

Regards,
Prasad DN
PS: Incase anyone wanted to know.

Drop the IFs and you can enter it without CSE
=SUM((MMULT((C3:E11>=F3:F11)/1,{1;1;1})>=1)/1)

 

[XOR LX]

@prasaddn, @Tiago MF Nice to see MMULT being put to good use! Well done you two!

@Tiago MF Interesting technique for coercing to numerics! Don't think I've ever seen division by unity before. Nice to see something different for a change! (Sometimes I think I'll scream if I see another double-unary, i.e. -- . I tend to vary my choice, though right now I seem to be using N() a lot.)

Cheers