is there another alternative, IF SUMPRODUCT ?

[Blvkcage]

Hi all,
Need some info on "IF SUMPRODUCT" formula.
This formula help me validate my data for 1 year but this formula can slow down calculation considerably and take long time for me to report.
is there something better than if sumproduct?

An example of a formula is this:
=IF(SUMPRODUCT(($B$3:$B3=B3)*($B$3:$B3=B3))>1,0,1)

 

[maabadi]

Welcome to MrExcel Message Board
Test This

=IF(COUNTIF($B$3:$B3,B3)=1,1,0)

 

[Peter_SSs]

Welcome to the MrExcel board!

On of the reasons that your formula slows your sheet is that it is doing the identical cell comparisons twice. For example, in row 10 the formula becomes
=IF(SUMPRODUCT(($B$3:$B10=B10)*($B$3:$B10=B10))>1,0,1)
In that cell it compares eight cells (B3:B10) to B10 but then it compares those same eight cell to B10 again.

So you could roughly halve the calculation time by changing your formula to the following where it only does the comparison once in each formula.
=IF(SUMPRODUCT(--($B$3:$B3=B3))>1,0,1)

However, there are faster ways still.

I did a test with about 10,000 rows.
Your original formula took 9.6 seconds to calculate the column.
The adjusted formula that I suggested above took 4.8 seconds
maabadi's formula took 2.6 seconds
The formula below took 0.8 seconds, so I'd suggest that you give it a go.

=1-ISNUMBER(MATCH(B3,B$2:B2,0))

 

[Blvkcage]

Welcome to the MrExcel board!

On of the reasons that your formula slows your sheet is that it is doing the identical cell comparisons twice. For example, in row 10 the formula becomes
=IF(SUMPRODUCT(($B$3:$B10=B10)*($B$3:$B10=B10))>1,0,1)
In that cell it compares eight cells (B3:B10) to B10 but then it compares those same eight cell to B10 again.

So you could roughly halve the calculation time by changing your formula to the following where it only does the comparison once in each formula.
=IF(SUMPRODUCT(--($B$3:$B3=B3))>1,0,1)

However, there are faster ways still.

I did a test with about 10,000 rows.
Your original formula took 9.6 seconds to calculate the column.
The adjusted formula that I suggested above took 4.8 seconds
maabadi's formula took 2.6 seconds
The formula below took 0.8 seconds, so I'd suggest that you give it a go.

=1-ISNUMBER(MATCH(B3,B$2:B2,0))

hi peter thanks alot for yourhelp ,
another question if the criteria more than 1 row (maybe 2 or 3 row) like image below, what i should to do with this formula "=1-ISNUMBER(MATCH(B3,B$2:B2,0))"

coz i just can do this =IF(COUNTIFS($F$3:$F3,F3,$G$3:G3,G3)=1,1,0) what more took faster..
can u help please.

 

[Blvkcage]

Welcome to MrExcel Message Board
Test This

=IF(COUNTIF($B$3:$B3,B3)=1,1,0)

thanks alot maabadi, its help me alot

 

[Peter_SSs]

peter thanks alot for yourhelp ,

You're welcome.

another question if the criteria more than 1 row

I think you mean more than one column?
If so, I think your current COUNTIFS formula is probably the fastest

BTW, you will generally get faster/better help if you provide your sample data with XL2BB so that helpers can copy it to test with.