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

Pommy

New Member
Joined
Dec 4, 2016
Messages
10
=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.
 
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?
 
Upvote 0
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.


 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
11​
[/td][td][/td][td][/td][td]
6001​
[/td][td]
5008​
[/td][td]
4300​
[/td][td]
8000​
[/td][td][/td][td][/td][/tr]
[tr][td]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]


Option 1.

=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)

Option 2.

=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)

Both yield the same result.
 
Upvote 0
No need for the control+shift+enter. SumProduct does that implicitly... However, I'm amazed:

[TABLE="class: grid"]
<tbody>[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​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
6001​
[/TD]
[TD]
5008​
[/TD]
[TD]
4300​
[/TD]
[TD]
8000​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Option 1.

=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)

Option 2.

=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)

Both yield the same result.

Yes, i see they do, but alas it does not on mine, i'm afraid i can't offer any suggestions as to why either, perhaps it works differently if you refer to tables in other worksheets and those worksheets are 'hidden' ??? As is my case.
 
Upvote 0
Yes, i see they do, but alas it does not on mine, i'm afraid i can't offer any suggestions as to why either, perhaps it works differently if you refer to tables in other worksheets and those worksheets are 'hidden' ??? As is my case.

Just one more query if i may, what is the purpose of the +307 in the formula i am using, it's puzzling me.
 
Upvote 0
Just one more query if i may, what is the purpose of the +307 in the formula i am using, it's puzzling me.

MATCH(9.99999999999999E+307,U11:DZ11)

picks out the last numeric value and shows its position in U11:DZ11. The look up value 9.99999999999999E+307 is a constant of Excel itself which is an improbable value to occur in references of interest to us. MATCH and kindred functions, which recruit an algorithm similar to binary search, are bound looking for this look up value to hit the last numeric value in references they are fed with as a side effect of that algorithm. See for more:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top