Short the Formula

[SUHAS KARHADKAR]

Respected Excel Gurus,

Sir/s

I have defined a formula to get the sum. Now I want the formula to be short, is it possible?

Attached a excel file. (File Name - Test_1)

Awaiting reply/response.

Thanking you.

 

[Peter Bartholomew]

If you are using 365, you could use VSTACK to combine the ranges into one array

VSTACK('Flats-S1'!L4:L12,'Flats-S1'!L49,'Flats-S1'!L53)

It would also be possible to define a Lambda function 'Cleanλ' to turn the text amounts into numbers

= LAMBDA(text,
     IFERROR(
        VALUE(
           REDUCE(text, {",","/-"}, LAMBDA(t,s, SUBSTITUTE(t,s,"")))
        ),
     0)
   )

so that the worksheet formula would be

= SUM(Cleanλ(VSTACK(
    'Flats-S1'!L4:L12,
    'Flats-S1'!L49,
    'Flats-S1'!L53
  )))

It probably all looks somewhat alien, but it works with 365.

 

[p45cal]

You can do this with Power Query outputting to a Pivot table.
This allows you (in the background) to correct the Rs values in the table to proper numbers, and the pivot table allows you to group flats any way you want. No formulae required at all.
See pivot at cell B10 of the Consolidated sheet in the attached.

 

[SUHAS KARHADKAR]

Peter Bartholomew Sir,
Thank your very much, but I am using Excel 2016.

p4cal Sir,
Thank you, but I've to send my excel file and that person I suppose will get confused about pivot table. Any way thank you very much.

Thanks all.

 

[p45cal]

that person I suppose will get confused about pivot table

Then they sure as hell will get confused with your formula…

 

[bosco_yip]

Try this single copied across down formula

In "Consolidated" sheet C5, formula copied across right and down :

=SUMPRODUCT(SUMIFS(INDEX('Flats-S1'!$D$4:$R$167,,MATCH(C$4,'Flats-S1'!$D$3:$R$3,0)),'Flats-S1'!$D$4:$D$167,">="&MID($B5,{1,9,17},3),'Flats-S1'!$D$4:$D$167,"<="&MID($B5,{5,13,21},3)))



Remark :

1] "Consolidated" sheet headers must as same as the Source table (Flats-S1) sheet headers.

2] "Consolidated" sheet -"Flat no." format, please follow my revised in range B5:B7.

That is : 101 to 109 >> 101-109 and 401 >> 401-401 ( all flat no. put in pair joined with "-" & separated with comma "," and don't use line breaker)

3] Since my computer consider Source table sheet header with (in Rs.) value like 12,00,000/- as text, I changed it to number,

but If your computer can treat (in Rs.) value as number, that is fine, no need to change.

4] All highlighted in blue color cells content are changed by me, please refer to the attachment.

Regards

 

[SUHAS KARHADKAR]

Try this single copied across down formula

In "Consolidated" sheet C5, formula copied across right and down :

=SUMPRODUCT(SUMIFS(INDEX('Flats-S1'!$D$4:$R$167,,MATCH(C$4,'Flats-S1'!$D$3:$R$3,0)),'Flats-S1'!$D$4:$D$167,">="&MID($B5,{1,9,17},3),'Flats-S1'!$D$4:$D$167,"<="&MID($B5,{5,13,21},3)))

View attachment 79074

Remark :

1] "Consolidated" sheet headers must as same as the Source table (Flats-S1) sheet

2] "Consolidated" sheet -"Flat no." format, please follow my revised one,

That is : 101 to 109 >> 101-109 and 401 >> 401-401

3] Since my computer consider Source table sheet header with (in Rs.) value like 12,00,000/- as text, I changed it to number,

but If your computer can consider (in Rs.) value as number, that is fine, no need to change.

4] All highlighted in blue color cells content are changed, please refer to the attachment.

Regards

Thank your very much, bosco_yip Sir.