#### kelly1

##### Well-known Member
I am using this simple formula and getting #VALUE! error.

Can anyone suggest a solution please.

=I8+O8+U8+AA8+AG8+AM8+AS8+AY8+BE8+BK8+BQ8+BW8+CC8+CI8+CC8+CU8+DA8+DG8+DM8+DS8+DY8+EE8

Thanks for any help

Kelly

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### NBVC

##### Well-known Member
Are you sure that all those formatted as some sort of number, not text?

#### kelly1

##### Well-known Member
I've formatted as:
0;-0;;@
so it doesn't show zeros, but even if I format as numbers it still does the same

Regards

Kelly

#### NBVC

##### Well-known Member
Try copying the whole row and then do and Edit|Paste Special and choose Values.

Does that fix it?

I am pretty sure that it is a formatting issue.

Also try pressing F9 to make sure it is calculating.

##### MrExcel MVP

kelly1 said:
I am using this simple formula and getting #VALUE! error.

Can anyone suggest a solution please.

=I8+O8+U8+AA8+AG8+AM8+AS8+AY8+BE8+BK8+BQ8+BW8+CC8+CI8+CC8+CU8+DA8+DG8+DM8+DS8+DY8+EE8

Thanks for any help

Kelly

Do you have any formulas in those cells that return "", a blank?

#### kelly1

##### Well-known Member
Yes I do have formulas, but it did work before up to 15 additions, then I extended it to 22 and am now getting problems.

Examples

=IF(ISERROR(\$I\$1),"",SUMPRODUCT(--('User 01'!\$N\$3:INDEX('User 01'!\$N:\$N,\$I\$1)>=\$G8),--('User 01'!\$N\$3:INDEX('User 01'!\$N:\$N,\$I\$1) < \$G9),--('User 01'!\$M\$3:INDEX('User 01'!\$M:\$M,\$I\$1)=I\$7)))

=IF(Totals!\$H\$17<1,"",SUMPRODUCT(--('User 01'!\$I\$3:INDEX('User 01'!\$I:\$I,\$J\$1)>=\$G8),--('User 01'!\$I\$3:INDEX('User 01'!\$I:\$I,\$J\$1) < \$G9)))

.

##### MrExcel MVP
kelly1 said:
Yes I do have formulas, but it did work before up to 15 additions, then I extended it to 22 and am now getting problems.

Examples

=IF(ISERROR(\$I\$1),"",SUMPRODUCT(--('User 01'!\$N\$3:INDEX('User 01'!\$N:\$N,\$I\$1)>=\$G8),--('User 01'!\$N\$3:INDEX('User 01'!\$N:\$N,\$I\$1) < \$G9),--('User 01'!\$M\$3:INDEX('User 01'!\$M:\$M,\$I\$1)=I\$7)))

=IF(Totals!\$H\$17<1,"",SUMPRODUCT(--('User 01'!\$I\$3:INDEX('User 01'!\$I:\$I,\$J\$1)>=\$G8),--('User 01'!\$I\$3:INDEX('User 01'!\$I:\$I,\$J\$1) < \$G9)))

.

Kelly,

It's those formula blanks (i.e., "") which cause the #VALUE error. For example:

=""+3

cannot succeed for the + operator tries to coerce "" into a number and fails.

It looks like you want to sum every 6th value in I8:EE8. If so, try:

=SUMPRODUCT(--(MOD(COLUMN(I8:EE8)-COLUMN(I8)+0,6)=0),I8:EE8)

#### kelly1

##### Well-known Member

Thats done it ok

Regards

Kelly

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,553
Messages
5,838,075
Members
430,527
Latest member
MyFace2

### 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