Finding first blank cell within a (Sumproduct(Sumif( formula

Pommy · Dec 4, 2016

=ROUND((SUMPRODUCT(SUMIFS(Import_Balance_Sheet_I_and_E[TRANS_AMOUNT],Import_Balance_Sheet_I_and_E[NOM_CODE],U11:DZ11))),0)

The above formula works great, never any issues, however, there are thousands of them and as such it's slow. I'm hoping there is an alternative to having to specify the cell range U11:DZ11 and instead of saying that the last cell is DZ11, it would only go as far as the first blank cell. For instance in the above formula range U11:DZ11, only the first three columns are populated, ie. only U11 & V11 & W11 contain any data, all columns after that are blank, so ideally it would say U11:W11. But on the row below it, the first 75 columns are in use. So i'm effectively using a catch all range of U11:DZ11 forumla as i don't want to have to go through thousands of rows adjusting the U11:DZ11 to the specific number of columns, as this would take several days to achieve.

I was wondering if the Offset function would work, i'm utterly useless with Offset i hasten to add, despite reading many websites on how to use. A colleague suggested (i think) Index and Match, but again i have no idea how to use those functions either.

Any help appreciated.

Pommy · Dec 5, 2016

Aladin Akyurek said:
They are numbers, right, not text numbers?

The field in the SQL database is an alphanumeric field, however, they are only using numbers. The format on the spreadsheet is a number, i've added them together to check that they are recognised as numbers. Does that help?

Aladin Akyurek · Dec 5, 2016

Pommy said:
The field in the SQL database is an alphanumeric field, however, they are only using numbers. The format on the spreadsheet is a number, i've added them together to check that they are recognised as numbers. Does that help?

My understanding of the issue is that you want restrict the criteria range in the formula because that range is not always full. If so:

1.

Rich (BB code):

=ROUND(
    SUMPRODUCT(
      SUMIFS(
         Import_Balance_Sheet_I_and_E[TRANS_AMOUNT],
         Import_Balance_Sheet_I_and_E[NOM_CODE],U11:INDEX(U11:DZ11,MATCH(9.99999999999999E+307,U11:DZ11)))),0)

2.

Rich (BB code):

=ROUND(
     SUMPRODUCT(
       Import_Balance_Sheet_I_and_E[TRANS_AMOUNT],
       --ISNUMBER(MATCH(Import_Balance_Sheet_I_and_E[NOM_CODE],U11:DZ11,0))),0)

I'd opt for whichever improves the performance.

Pommy · Dec 5, 2016

Aladin Akyurek said:
My understanding of the issue is that you want restrict the criteria range in the formula because that range is not always full. If so:

1.

Rich (BB code):

=ROUND( SUMPRODUCT( SUMIFS( Import_Balance_Sheet_I_and_E[TRANS_AMOUNT], Import_Balance_Sheet_I_and_E[NOM_CODE],U11:INDEX(U11:DZ11,MATCH(9.99999999999999E+307,U11:DZ11)))),0)

2.

Rich (BB code):

=ROUND( SUMPRODUCT( Import_Balance_Sheet_I_and_E[TRANS_AMOUNT], --ISNUMBER(MATCH(Import_Balance_Sheet_I_and_E[NOM_CODE],U11:DZ11,0))),0)

I'd opt for whichever improves the performance.

I'm so grateful, your first one works beautifully, i just did a test on 100 rows using MY formula which took 3 mins 20 seconds, using your new formula it took just 12 seconds. I'm astounded.
The second of your formula using 'isnumber' returns zero's. I am happy that i have a working one now, thank you very much indeed, it truly is very much appreciated.

Aladin Akyurek · Dec 5, 2016

Pommy said:
I'm so grateful, your first one works beautifully, i just did a test on 100 rows using MY formula which took 3 mins 20 seconds, using your new formula it took just 12 seconds. I'm astounded.
The second of your formula using 'isnumber' returns zero's. I am happy that i have a working one now, thank you very much indeed, it truly is very much appreciated.

Glad to hear that we get better performance. I avoided using a defined criteria range in the second formula... Considering that we would get:

2. (Revised)

Rich (BB code):

=ROUND(
     SUMPRODUCT(
       Import_Balance_Sheet_I_and_E[TRANS_AMOUNT],
       --ISNUMBER(MATCH(Import_Balance_Sheet_I_and_E[NOM_CODE],U11:INDEX(U11:DZ11,MATCH(9.99999999999999E+307,U11:DZ11)),0))),0)

Not sure whether this would be faster than the SUMIFS version.

Pommy · Dec 5, 2016

Aladin Akyurek said:
Glad to hear that we get better performance. I avoided using a defined criteria range in the second formula... Considering that we would get:

2. (Revised)

Rich (BB code):

=ROUND( SUMPRODUCT( Import_Balance_Sheet_I_and_E[TRANS_AMOUNT], --ISNUMBER(MATCH(Import_Balance_Sheet_I_and_E[NOM_CODE],U11:INDEX(U11:DZ11,MATCH(9.99999999999999E+307,U11:DZ11)),0))),0)

Not sure whether this would be faster than the SUMIFS version.

Thanks again, i just tried your revised one and still i only get zero's, despite using CTRL SHFT ENTER too.

Aladin Akyurek · Dec 5, 2016

Pommy said:
Thanks again, i just tried your revised one and still i only get zero's, despite using CTRL SHFT ENTER too.

No need for the control+shift+enter. SumProduct does that implicitly... However, I'm amazed:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

U

[/td][td]

V

[/td][td]

W

[/td][td]

X

[/td][td]

Y

[/td][td]

Z

[/td][/tr][tr][td]

1

[/td][td="bgcolor:#5B9BD5"]TRANS_AMOUNT[/td][td="bgcolor:#5B9BD5"]NOM_CODE[/td][td]option 1[/td][td]option 2[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2

[/td][td="bgcolor:#DDEBF7"]

4.2

[/td][td="bgcolor:#DDEBF7"]

6001

[/td][td]

15

[/td][td]

15

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3

[/td][td]

6.78

[/td][td]

6001

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4

[/td][td="bgcolor:#DDEBF7"]

3.7

[/td][td="bgcolor:#DDEBF7"]

5008

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5

[/td][td]

0.8

[/td][td]

4300

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6