# Prioritize Columns to add non-blank cells per row

1. ## Prioritize Columns to add non-blank cells per row

I figure there's a pretty simple formula to resolve this.

I would like Excel to Sum only 1 non-blank cell per row, based on a column priority.

Column Priority in the sample below is E/C/D/A/B

So, the process would be:

Excel checks E2 (blank), then moves to C2 (blank), then moves to D2 (3) and stops.
Then in the next row, it checks E3 (blank), then moves to C3 (13) and stops.
In the 3rd row, it checks E4 (8) and stops.
In row 4, it checks E5 (1) and stops.
In row 5, it checks E6 (blank), then moves to C6 (11) and stops.
In row 6, it checks E7 (blank), then C7 (blank), then to D7 (blank), then to A7 (blank), then to B7 (7) and stops.

 a b c d e 12 10 3 11 6 13 6 8 8 9 25 1 5 17 11 8 7

Your help with this is much appreciated!

2. ## Re: Prioritize Columns to add non-blank cells per row

Why does row 6 not use 23 in column E, which is meant to be top priority?

Can we use helper columns?

3. ## Re: Prioritize Columns to add non-blank cells per row

Sorry. User error. I'll correct the table.

4. ## Re: Prioritize Columns to add non-blank cells per row

Could you answer my other question?

5. ## Re: Prioritize Columns to add non-blank cells per row

One way with a helper column (could be hidden):

Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
I
1
12
10
3
3
2
11
6
13
13
TOTAL:
43
3
6
8
8
8
4
9
25
1
1
5
5
17
11
8
11
6
7
7
 Sheet: Sheet2

Excel 2016 (Windows) 32 bit
F
1
=IF(E1<>"",E1,IF(C1<>"",C1,IF(D1<>"",D1,IF(A1<>"",A1,IF(B1<>"",B1,0)))))
 Sheet: Sheet2

Excel 2016 (Windows) 32 bit
I
2
=SUM(F1:F6)
 Sheet: Sheet2

7. ## Re: Prioritize Columns to add non-blank cells per row

Hi Ali,

I see how that works. Thanks.

Is there a solution that doesn't require a helper row?

Here's a more specific example for the formula I'm after.

I have a sheet that lists contractors, what job they are working on, how much their estimate is, if I hired them and how much it actually cost. Then, this table (which I have on a different tab) has formulas that pull the min and max for each job type. The estimate pulls the average for each job and the actual grabs the cost from ones I actually hire.

On my summary sheet, this is the table I have:

 Job Low High Estimate Actual Paint 1,500 4,500 Roof 8,500 15,000 Floor 2,000 8,000 6,000 Window 1,000 2,000 1,350 1,575 Landscape 7,500 18,000

If I need to have a helper column, then it's not a big deal. More so out of curiosity now, is there a formula that would sum the jobs conditionally? If there's an Actual entry, add that; otherwise, add the Estimated number. If none, take the Max, then Min. For the last 2 steps, an average of Min/Max works just as well.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1