I have numbers in Col F and values in Col H on sheet1. I am trying to add values in Col H where the range of numbers is from 3000 to 3015.
I have set up a sumproduct formula on Sheet 2 -The formula returns an incorrect value see A2. I have shouwn what the correct value should be in Cell C2
Attached please find sample data
It would be appreciated if you could assist & amend the formula so that it returns the correct answer
The formula is summing all of
3001 3000D 3000K 3000X 3002 3002D 3002K 3002X 3003 3003D 3003K 3003X 3004 3004D 3004K 3004X 3005 3005D 3005K 3005X 3006 3006D 3006K 3006X 3007 3007D 3007K 3007X 3008 3008D 3008K 3008X 3009 3009D 3009K 3009X 3015 3015D 3015K 3015X
And the value should be (2,175,478)
To get the value you are looking for, you could use
=SUM(IFERROR((--Sheet1!F1:F4310)>=3000,0)*IFERROR((--Sheet1!F1:F4310)<=3015,0)*(Sheet1!H1:H4310)) as array formula
Last edited by Excel Fox; 04-06-2013 at 05:19 PM. Reason: Additional Info
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Thanks for the help, much appreciated. There were blank values resulting in the error. I will post a new post showing you what I need
Your assistance in resolving this is most appreciated
Regards
Howard
Last edited by Howardc; 04-07-2013 at 11:35 AM.
Hi Admin
My Apologies. I rechecking the attached file, I found that a number of values had blanks. Your formula works perfectly.
Regards
Howard
Hi Admin
I would be appreciated if you could amend the formula in cell C6 on sheet "account classification" -see attached workbook. I have set up your formula which gives me an incorrect result. I have also set a a Vlookup to give me the values per account number on sheet "account classification" pertaining to the values on sheet1 for the range 3000 to 3015 (four digit account numbers only)
1) Adding Range of account numbers 3000 to 3015 -where the account numbers are in Col C and values Col E i.e four digit account numbers only
2) Range of account numbers 3000D to 3015D -where the account numbers are in Col C and values Col E i.e five digit account numbers ending in D eg 3001D
Your assistance in resolving this is most appreciated
Last edited by Howardc; 04-07-2013 at 01:33 PM.
Can you post a sample with the expected result?
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Thanks, it works perfectly
The formula see C29 Sheet Account classification works perfectly
However, when I change increase the range from 1000 to say 3000 , it give me zero as the result
=SUM(IFERROR((INT(Sheet1!$C$5:$C$1000) >= 3000)*(INT(Sheet1!$C$5:$C$1000) <= 3015)*(Sheet1!$E$5:$E$1000),0))
When trying to add the range 3000D to 3015D, it gives me #value! see cell C32 sheet account classification"
It would be appreciated if you could assist
Try this
=SUM(IFERROR((INT(SUBSTITUTE(Sheet1!$C$5:$C$1000," D","0")) >= 30000)*(INT(SUBSTITUTE(Sheet1!$C$5:$C$1000,"D","0" )) <= 30150)*(Sheet1!$E$5:$E$1000),0)) as array formula
A dream is not something you see when you are asleep, but something you strive for when you are awake.
It's usually a bad idea to say that something can't be done.
The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve
Join us at Facebook
Hi
Thanks for the help. I removed the space before "D" =SUM(IFERROR((INT(SUBSTITUTE(Sheet1!$C$5:$C$1000," D","0")) to =SUM(IFERROR((INT(SUBSTITUTE(Sheet1!$C$5:$C$1000," D","0"))
I have tried to understand this formula and I understand all the components making up the formula , except how INT works in the formula. I have never used INT. I know the the INT function round a number to the nearest integer, but i'm not sure what this is trying to achieve in this formula
It would be appreciated if you could explain this to me
Regards
Howard
Last edited by Howardc; 04-07-2013 at 05:41 PM.
Posting Permissions
Reply With Quote
Bookmarks