Sumproduct in combination with other functions but excluding blank cells in range

**divr** · 01-11-2018, 05:40 AM

Hello everybody,

i am struggling with following function:

=SUMPRODUCT(--(LEFT(B5:I5,FIND(":",B5:I5)-1)))&":"&SUMPRODUCT(--(RIGHT(B5:I5,LEN(B5:I5)-FIND(":",B5:I5))))

it perfectly works if all cells in range are non-empty, but how to include condition if i want to skip empty/blank cells in this case?

To introduce what i want with this calculation, i have matrix (columns, rows) with 8 players and when players have played match there are results in the format e.g. 3:2, if not there are still empty cells. Based on current results i want to calculate total SCORE.

So if i have results: 1:1; 2:1; empty, 3:2 etc and by applying abovementioned formula i want to calculate that current score is 6:4 (and to skip EMPTY one).

i have found lot of examples by using <>"" to exclude empty ones, but it doesnt work.

thanks a lot

**Glenn Kennedy** · 01-11-2018, 05:42 AM

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**divr** · 01-11-2018, 05:48 AM

Hello Glenn,

here is the original file and in column J i want to calculate that by skipping empty cells (matches with green 0:0 have not been played).

thanks
d

**shukla.ankur281190** · 01-11-2018, 05:50 AM

Try

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

**divr** · 01-11-2018, 05:57 AM

Perfect!!! thanks a lot

)

d.

**shukla.ankur281190** · 01-11-2018, 06:15 AM

Happy to help you

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Sumproduct in combination with other functions but excluding blank cells in range

LinkBack

Thread Tools

Rate This Thread

Display

Sumproduct in combination with other functions but excluding blank cells in range

Re: Sumproduct in combination with other functions but excluding blank cells in range

Re: Sumproduct in combination with other functions but excluding blank cells in range

Re: Sumproduct in combination with other functions but excluding blank cells in range

Re: Sumproduct in combination with other functions but excluding blank cells in range

Re: Sumproduct in combination with other functions but excluding blank cells in range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Looking for final four values in range, excluding any blank cells

[SOLVED] Excluding blank cells

Group combination of cells based on manual logic or functions

Excluding blank cells from a range of data

[SOLVED] SUMPRODUCT Problem with blank non blank cells included in range and ever changing range

Excel 2007 : Excluding Blank Cells

Simple Sumproduct of 2 Columns, with some blank cells in range

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions