• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array Formula should return 1 not 0 when it finds an equal value.

G

GN0001

Member
I have an array:

These are values inside my array:

May

April

June

September

The array is extended between: A1 to A4

this is my formula and I enter it to cell B2:

=Sum((A1:A4)=A3) and I press ctrl + shift+ enter. In understand A1 to A4 is an array and has a bunch of falses and trues. It needs to return 1, since we can find value of A3 in the array. But it keeps evaluating this formula to 0. What is the reason?

Thank you for your help.
 
You need to coerce the True/False values to numbers


=SUM(--(A1:A4=A3))


But there is no need for an array formula, you can simply use COUNTIF


=COUNTIF(A1:A4,A3)
 
Thank you for your help. But I realized my answer as soon as I posted the question, because hui had dexplained it to me in detail before.

Regards,

Guity
 
You must log in or register to reply here.
Back
Top