Looking for Array formula to count the records where two fields are equal

[General Ledger]

Dear All,

I am looking for an array formula to return the number of times in a list of records that two fields within each record are equal.

For example: Column A is the field "Beginning". Column B is the field "Ending". On each line, either the values in columns A and B are equal or they are not equal. I need to count the number of records in the list where the values are equal.

Thanks,

GL

 

[Aladin Akyurek]

Dear All,

I am looking for an array formula to return the number of times in a list of records that two fields within each record are equal.

For example: Column A is the field "Beginning". Column B is the field "Ending". On each line, either the values in columns A and B are equal or they are not equal. I need to count the number of records in the list where the values are equal.

Thanks,

GL

Either...

Control+shift+enter, not just enter:

=SUM(IF(A2:A100=B2:B100,1))

Or just enter:

=SUMPRODUCT((A2:A100=B2:B100)+0)

 

[VoG]

Try like this

=SUMPRODUCT(--(A1:A100=B1:B100),--(A1:A100<>""))

 

[General Ledger]

Aladin,

You provided a great solution to the exact question I asked. Thanks so much.

VoG,

You answered the question I should have asked. Your formula considered that the number of records I was examining may not be known at the time the formula is created. Your solution is flexible enough to work on all the rows in an Excel sheet regardless of the range of records and ignores blank rows.

You guys really rock!

Thanks,

GL