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
 

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
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,210

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,210
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)
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top