• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get the Last column Letter formula

R

Raghava@rock

Member
Hi ,

I am using sumif formula (=SUMIF($BD$15:$GW$15,$AV$15,$BD17:$GW17) , however in the current formula last column is GW , but when there is increase in range from GW to NI ,i need teh formula to get the rage updated to (=SUMIF($BD$15:$NI$15,$AV$15,$BD17:$NI17).

Appreciate your help in this regard.
 
Hi Ragav,

try the below

SUMIF($BD$15:INDEX($BD$15:$XX$15,COUNTA($BD$15:$XX$15)),$AV$15,$BD17:INDEX($BD$17:$XX$17,COUNTA($BD$17:$XX$17)))

Share the sample file if otherwise
 
Hi ,

From what I can see , if only one address is used , Excel uses that as the starting point and takes the end point from the first parameter.

But for correct results , the starting point row / column of the third parameter should match the starting point row / column of the first parameter.

See the attached file.

Narayan
 

Attachments

  • bb example.xlsx
    17.3 KB · Views: 6
I posted on this feature of SUMIF recently:

http://excelxor.com/2016/01/08/incrementing-indirect-column-references-within-sumifscountifs/

and it was also used to good effect in the latest Shortest Formula Challenge:

http://excelxor.com/2016/01/26/shortest-formula-challenge-6-scrabbled/

This property also appears to be unique to SUMIF within that group of functions: a similar syntax cannot be employed with SUMIFS, COUNTIF or COUNTIFS.

And it should be pointed out that reducing the sum_range like this has the disadvantage that it renders the construction volatile. As such, it is not really to be recommended (other than in shortest formula challenges, of course! :)).

Regards
 
Hi All,

I tried to find out any documentation around this, however, couldn’t find any…

What is creating confusion now is the fact that it should return 0 for cell D1 and D2..since we are looking for a criteria equal to B*

As per the attached in post no 8

I think there is more to it than I could understand about this construct..

Edit: Noticed little late that @XOR LX has responded
 
NARAYANK991 said:
From what I can see , if only one address is used , Excel uses that as the starting point and takes the end point from the first parameter.
Click to expand...

Not quite. The two ranges must still be of the same dimension. The sum_range is redimensioned to match that of the range.

So, for example:

=SUMIF(A1:A10,"X",B2)

is equivalent to:

=SUMIF(A1:A10,"X",B2:B11)

not:

=SUMIF(A1:A10,"X",B2:B10)

(In fact, the latter will also be redimensioned such that it matches the former.)

Regards
 
Narayan Sir, interesting test.

It produce some unexpected / wrong results, if we reverse the above technique, i.e. single cell reference in Range, and complete range reference in sum_range, like:

=SUMIF(A9,"B" & "*",$B$9:$B$17)

SUMIF Range.PNG

Result is 51400.96 in E3, while the criteria is AGU :confused:
 
The principle is no different here: the sum_range is again redimensioned such that it matches the dimensions of the range.

So:

=SUMIF(A11,"B" & "*",$B$9:$B$17)

for example, is treated just as if it were:

=SUMIF(A11,"B" & "*",$B$9)

etc.

Regards
 
I have tried as below

=SUMIFS(BD185:INDEX($185:$185,MATCH("ZZZZ",$185:$185)),BD$11:INDEX($11:$11,MATCH("ZZZZ",$11:$11)),"Not Billed",BD$15:INDEX($15:$15,MATCH("ZZZZ",$15:$15)),"DTE Net Activity")

resulting value error
 
Raghava@rock said:
I have tried as below

=SUMIFS(BD185:INDEX($185:$185,MATCH("ZZZZ",$185:$185)),BD$11:INDEX($11:$11,MATCH("ZZZZ",$11:$11)),"Not Billed",BD$15:INDEX($15:$15,MATCH("ZZZZ",$15:$15)),"DTE Net Activity")

resulting value error
Click to expand...
Try,

=SUMIFS(BD185:INDEX($185:$185,MATCH(9.99E+307,$185:$185)),BD$11:INDEX($11:$11,MATCH("zzzz",$11:$11)),"Not Billed",BD$15:INDEX($15:$15,MATCH("zzzz",$15:$15)),"DTE Net Activity")
 
@Raghava@rock

Do you really need to determine the last-used column?

COUNTIF(S)/SUMIF(S) are rare among Excel functions in that they suffer no detriment to performance if an arbitrary number of rows/columns are referenced, so limiting the ranges passed, although good practice in general, is not actually necessary with these functions.

As such, you could use simply:

=SUMIFS(BD185:XFD185,BD11:XFD11,"Not Billed",BD15:XFD15,"DTE Net Activity")

And it's even simpler if we can include the columns prior to BD also without affecting the result, viz:

=SUMIFS(185:185,11:11,"Not Billed",15:15,"DTE Net Activity")

Regards
 
Hi Xor lx,

I tried using the above but the same is resulting the memory issues as the formula needs to be applied for 500 rows.

Do you have an alternative for the same
 
@Raghava@rock

Did you use the precise formula as I posted?

If so, even copied to 500 rows should mean a full calculation of no more than a second.

In fact, I did a quick test in a mocked-up workbook, entering dummy data in columns BD to XFD for a total of 500 rows. I then entered the formula I gave you and copied down a further 499 rows. A full calculation took just over half a second.

Are you sure that the issue is this particular set of formulas (doubtful given the results I get) and not some others within the workbook?

Regards
 
You must log in or register to reply here.
Back
Top