# Formulas Returning Wrong Values

#### CARBOB

##### Well-known Member
Will someone please tell me why the formulas in G:K are returning wrong values. They are suppose to count how many of the values in B:F repeat. I think it may be a format problem, but can't find it.

Book1
ABCDEFGHIJK
1412345
1512/30/16031417253611111
1612/29/16030513233611111
1712/28/16071422343511111
1812/27/16050913151711111
1912/26/16132630333611111
LAST DIGIT (2)

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### AliGW

##### Banned
Well, without seeing the formulae, I'm not sure we can tell! What are your expected results?

#### CARBOB

##### Well-known Member
Well, without seeing the formulae, I'm not sure we can tell! What are your expected results?

The formulas are not showing for some reason, don't understand why not.

starting in G15
=SUMPRODUCT(IF(COUNTIF(OFFSET(\$B15,1,0,G\$14,5),\$B15:\$F15)<COUNTIF(\$B15:\$F15,\$B15:\$F15),COUNTIF(OFFSET(\$B15,1,0,G\$14,5),\$B15:\$F15)/COUNTIF(\$B15:\$F15,\$B15:\$F15),1))

1 2 3 4 5
12/30/16 03 14 17 25 36 1 1 1 1 1
12/29/16 03 05 13 23 36 1 1 1 1 1
12/28/16 07 14 22 34 35 1 1 1 1 1
12/27/16 05 09 13 15 17 1 1 1 1 1
12/26/16 13 26 30 33 36 1 1 1 1 1

#### AliGW

##### Banned
Thanks. What are your expected results? The formula seems incomplete - is there something missing at the end?

Last edited:

#### MARK858

##### MrExcel MVP
@AliGW the complete formula the OP posted is...

=SUMPRODUCT(IF(COUNTIF(OFFSET(\$B15,1,0,G\$14,5),\$B15:\$F15)<COUNTIF(\$B15:\$F15,\$B15:\$F15),COUNTIF(OFFSET(\$B15,1,0,G\$14,5),\$B15:\$F15)/COUNTIF(\$B15:\$F15,\$B15:\$F15),1))

Last edited:

#### CARBOB

##### Well-known Member
I installed the HTML MAKER as an add-in, checked default and show formulas,is that right or wrong?

#### AliGW

##### Banned
Thanks, Mark, but how do you know that?

#### AliGW

##### Banned
I installed the HTML MAKER as an add-in, checked default and show formulas,is that right or wrong?

#### MARK858

##### MrExcel MVP
Thanks, Mark, but how do you know that?

Click reply with quote and you can normally see the full formula when Vbulletin is treating the < as a Html tag.

#### AliGW

##### Banned
Oooh, nice trick - thanks for that!

Doesn't work here in Firefox: I just get an empty message box.

Last edited:

Replies
10
Views
780
Replies
4
Views
3K
Replies
0
Views
544
Replies
10
Views
1K
Replies
12
Views
1K

1,195,936
Messages
6,012,394
Members
441,695
Latest member
MickRobertson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back