Sumproduct Formula To Sum Range With Criteria On Numeric Text

[Howardc]

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

[Excel Fox]

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

[Howardc]

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

[Howardc]

Hi Admin

My Apologies. I rechecking the attached file, I found that a number of values had blanks. Your formula works perfectly.

Regards

Howard

[Howardc]

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

[Excel Fox]

Can you post a sample with the expected result?

[Excel Fox]

What about http://www.excelfox.com/forum/f2/sumif-formula-addition-based-alphanumeric-criteria-883/

[Howardc]

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

[Excel Fox]

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

[Howardc]

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

[Excel Fox]

Oh the space in " D" was a miss. Anyway, glad it worked for you.

The INT is just meant to convert the text values to numeric values. So, for example, INT("100") will give numeric value 100.

[Howardc]

Hi

Thanks once again for your help & explanation which is much appreciated

Regards

Howard