HELP CONVERT SUMPRODUCT to COUNTIFS

so my formula is this

=SUMPRODUCT(--(B27:B8999<>E27:E8999))

how to convert this to the proper countifs?

the logic to my formula is it is suppose to count how many records of Column B has changed in Column E.

TIA.

Originally Posted by daddylonglegs

You can't do that with COUNTIFS because COUNTIF/COUNTIFS will compare each value in col B against a fixed criteria - you effectively have a different criteria for every row - best to stick with your current formula, is it working OK?

yes it's working fine on my excel but for other versions of excel it doesn't work. Im currently using excel 2007.

If possible attach the sample file

Any other formulas other than sumproduct that I could use then besides countif to count changes between 2 columns?

Originally Posted by daddylonglegs

There aren't really any better ways, or any need to change as far as I know. SUMPRODUCT is often used because it avoids the need for an array formula. All other options are very similar, e.g.

=SUM(--(B27:B8999<>E27:E8999))

but needs to be confirmed with CTRL+SHIFT+ENTER

or this similar version

=SUM(IF(B27:B8999<>E27:E8999,1))

also confirmed with CTRL+SHIFT+ENTER

....or just use a helper column, e.g. in Z27 copied down

=IF(B27<>E27,1,0)

now just sum column Z for the total number

thank you so much now it works fine!