Problem with simple addition

kelly1

Well-known Member
Joined
May 11, 2003
Messages
525
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
 

Some videos you may like

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
Joined
Aug 31, 2005
Messages
5,828
Are you sure that all those formatted as some sort of number, not text?
 

kelly1

Well-known Member
Joined
May 11, 2003
Messages
525
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
Joined
Aug 31, 2005
Messages
5,828
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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
Joined
May 11, 2003
Messages
525
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)))


.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,236
Messages
5,571,050
Members
412,359
Latest member
misstoffeepenny
Top