Formula to add values

[Howardc]

I have account mumbners in Col E for eg 3000 to 3199 and 3000D to 3199D. The values are in Col F

I would like a formula to add up all the values in Col F based in the account nuber series in COl E 3000 to 3199 and a seerate formula to add the values in Col F pertaining to the account number range 3000 to 3199D in Col E

Your assistance in this regard is most appreciated

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=Ugz39PGfytiMUCmTPTl4AaABAg. 91d_Pbzklsp9zfGbIr8hgW
https://www.youtube.com/watch?v=zHJPliWS9FQ&lc=UgwbcybM8fXnaIK-Y3B4AaABAg.97WIeYeaIeh9zfsJvc21iq
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zciSZa959d
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgzTC8V4jCzDHbmfCHF4AaABAg. 9zaUSUoUUYs9zckCo1tvPO
https://www.youtube.com/watch?v=vSjTzhoJFdk&lc=UgwMsgdKKlhr2YPpxXl4AaABAg
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwTUdEgR4bdt6crKXF4AaABAg. 9xmkXGSciKJ9xonTti2sIx
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgwWw16qBFX39JCRRm54AaABAg. 9xnskBhPnmb9xoq3mGxu_b
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9xon1p2ImxO
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgybZfNJd3l4FokX3cV4AaABAg. 9xm_ufqOILb9xooIlv5PLY
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgzgWvzV-kvC4TJ8O414AaABAg.9xnFzCj8HRM9y38bzbSqaG
https://www.youtube.com/watch?v=XQAIYCT4f8Q&lc=UgyWm8nL7syjhiHtpBF4AaABAg. 9xmt8i0IsEr9y3FT9Y9FeM
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9xpn-GDkL3o
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=UgzlC5LBazG6SMDP4nl4AaABAg. 9zYoeePv8sZ9zYqog9KZ5B
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg. 9xhyRrsUUOM9zYlZPKdOpm
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

[Excel Fox]

Will =SUMIF(B1:B112,"<3199",C1:C112) and =SUMIF(B1:B112,"*D",C88:C112) suffice?

[Howardc]

Thanks for the reply

1) The formula will need to add all values from 3000 to 3199
2) I need a seperate formula to add the values containing a D at the end for eg 3000D, 3001D, 3110D etc

[Rick Rothstein]

Thanks for the reply

1) The formula will need to add all values from 3000 to 3199
2) I need a seperate formula to add the values containing a D at the end for eg 3000D, 3001D, 3110D etc

Give these formulas a try...

Numbers without "D": =SUMPRODUCT(F1:F1000*(E1:E1000>2999)*(E1:E1000<320 0))

Numbers with "D": =SUMPRODUCT(F1:F43*(1*REPLACE(E1:E43,LEN(E1:E43),1 ,"")>2999)*(1*REPLACE(E1:E43,LEN(E1:E43),1,"")<320 0))

Note: The first formula will work if the specified range is larger than actual range of data; however, the second formula won't... its range must be sized to the actual range of data (blanks will make the formula return an error).