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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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

Thats done it ok

Regards

Kelly

Replies
2
Views
196
Replies
19
Views
1K
Replies
9
Views
487
Replies
4
Views
5K
Replies
9
Views
536