HELP CONVERT SUMPRODUCT to COUNTIFS

**chie** · 03-09-2013, 06:53 AM

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.

**daddylonglegs** · 03-09-2013, 06:56 AM

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?

**wfm007** · 03-09-2013, 06:57 AM

If possible attach the sample file

**chie** · 03-09-2013, 07:05 AM

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.

**daddylonglegs** · 03-09-2013, 07:14 AM

Your formula should work OK in all excel versions - did you test it in another version?

**chie** · 03-09-2013, 07:17 AM

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

**martindwilson** · 03-09-2013, 07:26 AM

why are you so anti sumproduct?

**daddylonglegs** · 03-09-2013, 07:28 AM

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

**chie** · 03-09-2013, 07:35 AM

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!