Cell formula to select values of first blank cell in a row and then the next four cells
Hi,
I am need a formula in the cell BG (no array formula please) that searches through the current row starting from column G through BF for the first BLANK cell and returns the total values from the cell to the right of Blank cell and the next 3 cells to the right of it)
For example: M4 is the first blank cell in row 4, then I need to get the total of (L4 through I4) which would be 13.
G4=5
H4=2
I4=3
J4=1
K4=4
L4=5
M4=BLANK
N4:BF4=BLANKS
Thanks in advance for any help.
Maybe this...
=SUM(BF4:INDEX(G4:BF4,MATCH(1E100,G4:BF4)-3))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Tony,
Thanks, but this formula didn't work. By the way, what is the "1E100" meant to do in your formula?
Tony,
My bad! Your formula is partially working. in the following case, the formula didn't work. The sum should have return 7, but is returing VALUE error. The formula needs to sum any non-blanks in the range of G4:BF4 for example.
G4=5
H4=2
I4:BF4=BLANKS
Please restate what you want to do.
In the 1st post in the 1st paragraph you say:
But in the next paragraph you show and describe an example that goes to the left.from the cell to the right of Blank cell and the next 3 cells to the right of it
Do you want to sum the 4 right-most numbers in the range of cells?
Are the numbers entered in a contiguous range?
Tony,
Sorry if I was not clear. Still we need the sum result from the four left cells next to a blank cell. Range (G4:BF4) will always have numeric values. At times, a row may only have 1 or 2 non-blanks starting from column G. So the sum needs to account for it and calculate result based on that. If a row has non-blank values from column G through L, then the sum needs to get the values from columns (I through L). Please let me know if I am still not clear and thanks for your help.
Last edited by labkhand; 07-08-2013 at 10:04 AM.
Can you post a SMALL sample file (10 to 15 rows worth of data will do) and show us what result you expect for each row?
Tony,
Attached is the sample file with the "SUM" column showing needing results.
Last edited by labkhand; 07-08-2013 at 12:19 PM.
Try this...
Assuming there will always be at least 1 number on a row...
Enter this formula entered in BG3 and copied down:
=SUM(BF3:INDEX(G3:BF3,MATCH(1E100,G3:BF3)-MIN(3,COUNT(G3:BF3))))
Perfect! Thanks very much!
Last edited by labkhand; 07-08-2013 at 12:29 PM.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks