My data looks like this:
A B
12000 100
14000 200
31000 500
33000 600
What is the formula I should use for this:
If left(A,1>1) then add column B
so that result = 1100
Some more explanation of the problem:
If the first digit of any number in Col A is greater than 1 (eg. those starting with 3) then add up the amounts in Col B. So that the answer returns 500 + 600 = 1100.
Last edited by ashtanga; 10-08-2010 at 03:57 PM.
you talking about first row?
Well, left is 1 and it's not bigger then 1... So why is B1 1100?
Never use Merged Cells in Excel
Not sure if I understand your question but here's what I've thought of...
=IF(LEFT(A1,1)>1,(LEFT(A1,1)&B1)*1,B1)
or
=IF(LEFT(A1,1)>=1,(LEFT(A1,1)&B1)*1,B1)
Perhaps.....
=SUMIF(A:A,">=20000",B:B)
Audere est facere
Are the numbers in Column A always 5 digits?
yes it is always 5 characters
found this on another forum which works great:
=Sum(If(Left(A1:A4,1)>"1",B1:B4,0))
I believe dll's solution does what you requested - and more efficiently than the alternative you mention.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks