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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Are you sure that all those formatted as some sort of number, not text?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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)))


.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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
Back
Top