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

[Pommy]

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

 

[arthurbr]

It looks like the SUMPRODUCT function in your formula does not serve a purpose

 

[Pommy]

I assure you, it will not work without it, i just tried. At first i was inclined to agree with you, but i I gave it a try and will not return the correct value.

 

[Aladin Akyurek]

Try...

=ROUND((SUMPRODUCT(SUMIFS(Import_Balance_Sheet_I_and_E[TRANS_AMOUNT],Import_Balance_Sheet_I_and_E[NOM_CODE],U11:INDEX(U11:DZ11,MATCH(REPT("z",255),U11:DZ11))))),0)

 

[Aladin Akyurek]

Try also...

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

 

[Pommy]

Try...

=ROUND((SUMPRODUCT(SUMIFS(Import_Balance_Sheet_I_and_E[TRANS_AMOUNT],Import_Balance_Sheet_I_and_E[NOM_CODE],U11:INDEX(U11:DZ11,MATCH(REPT("z",255),U11:DZ11))))),0)

Thanks for your efforts. This one does a calculation, however, it doesn't give me the correct figure.

 

[Pommy]

Try also...

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

Thanks again. However, this one returns an 'Excel Window' error message saying something wrong with Formula.

 

[Aladin Akyurek]

Thanks for your efforts. This one does a calculation, however, it doesn't give me the correct figure.

The U11:INDEX(U11:DZ11,MATCH(REPT("z",255),U11:DZ11)) bit checks where text data ends in U11:DZ11. The assumption is that U11:DZ11 is text and contains no formulas. If this is not the case, try to describe what you have in this criteria range.

Thanks again. However, this one returns an 'Excel Window' error message saying something wrong with Formula.

A paren too many... Edited:

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

 

[Pommy]

The U11:INDEX(U11:DZ11,MATCH(REPT("z",255),U11:DZ11)) bit checks where text data ends in U11:DZ11. The assumption is that U11:DZ11 is text and contains no formulas. If this is not the case, try to describe what you have in this criteria range.



A paren too many... Edited:

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

Thanks. The values in U11 to DZ11 are 4-digit numbers, eg
[TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl1605, width: 42, align: right"]6011[/TD]
[TD="class: xl1605, width: 42, align: right"]6008[/TD]
[TD="class: xl1605, width: 42, align: right"]6009[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Thanks. The values in U11 to DZ11 are 4-digit numbers, eg
[TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl1605, width: 42, align: right"]6011[/TD]
[TD="class: xl1605, width: 42, align: right"]6008[/TD]
[TD="class: xl1605, width: 42, align: right"]6009[/TD]
[/TR]
</tbody>[/TABLE]

They are numbers, right, not text numbers?