HELP REQUIRED FOR ANALYZING DATA

[damorashish]

Dear All,

I am new to this forum. I have data as per enclosed table but it is not as per the required format. Please help me get some formula so the data can be arranged as per required order for analyzing. I will be highly obliged. Sample DATA TABLE AND REQUIRED FORMAT enclosed.

 

[Luke M]

Welcome to the forum!

Your attached file is hard to understand as it only has one sheet with info. Is that in the desired output, or how the input looks? It currently looks like the layout of a PivotTable. Please clarify what you have vs. what you want.

 

[bosco_yip]

Your enclosed file does not content "Required Format",

please re-upload your file to show what do you wanted format.

Regards
Bosco

 

[damorashish]

Dear Sir,

Sorry for inconvenience. I have submitted wrong file. Please find enclosed correct file.

 

[bosco_yip]

In I3, copied across and down :

=SUMIFS(IF($H3="Premium",$C:$C,$D:$D),$A:$A,$G3,$B:$B,I$2)

Regards
Bosco

 

[damorashish]

Dear Bosco sir,

Thanks for your help. Will check and confirm.

 

[damorashish]

Dear Bosco Sir,

AWESOME. You have saved me lots of man hours and provided me mental peace....

Regards,

Ashish

 

[damorashish]

Dear Sir,

The formula has helped me immensely but when I copy this formula to another file why it does not work?

 

[GraH - Guido]

Did you check if
- the data is structured in the same way in both files -> if not the formula may refer to the wrong columns, thus it is not working.
- with the copy you are actually referring the the workbook you've copied from?

Perhaps copy formula in the cell (F2, CTRL+A , CTRL+C) and then past in the new file.

 

[bosco_yip]

Dear Sir,

The formula has helped me immensely but when I copy this formula to another file why it does not work?

Unmerge the cells of Column A as per my file, you will discover the trick.

Regards
Bosco

 

[deciog]

Bosco Good Morning.

When I undo the merge, it appears in the cell = A2 ... = A14, very interesting, but when I do in my Excel version 385 2016, it in the merge leaves only the first cell or A2, so I ask, how does this trick ?.

Thank you Decicio

 

[damorashish]

Bosco Good Morning.

When I undo the merge, it appears in the cell = A2 ... = A14, very interesting, but when I do in my Excel version 385 2016, it in the merge leaves only the first cell or A2, so I ask, how does this trick ?.

Thank you Decicio

Dear Bosco,

I am also facing same problem. Please explain

 

[damorashish]

Dear Bosco,

Sorry to bother you once again. Please find my excel enclosed. In this file if the concerned department is mentioned in first row then the answer will come as per formula but for other department it does not come. There is very long list of agents and I have to copy agent name in each row for getting correct answer. Please help

 

[bosco_yip]

Dear Bosco,

Sorry to bother you once again. Please find my excel enclosed. In this file if the concerned department is mentioned in first row then the answer will come as per formula but for other department it does not come. There is very long list of agents and I have to copy agent name in each row for getting correct answer. Please help

The trick to fill range A2:B25 blank cells with data

1] Copy A2:B25 format and Paste to unused range T2:U25

2] Select A2:A25 >> Unmerge cells of A2:A25 >> press "F5" >> click "Special " >> click "Blank" >> OK >> enter: =A2 >> press Ctrl+Enter

3] Copy T2:U25 format and Paste back to A2:B25 >> delete column T and column U >> Finish


Then, the Sumifs formula will return the desired result

Regards
Bosco

 

[damorashish]

The trick to fill range A2:B25 blank cells with data

1] Copy A2:B25 format and Paste to unused range T2:U25

2] Select A2:A25 >> Unmerge cells of A2:A25 >> press "F5" >> click "Special " >> click "Blank" >> OK >> enter: =A2 >> press Ctrl+Enter

3] Copy T2:U25 format and Paste back to A2:B25 >> delete column T and column U >> Finish


Then, the Sumifs formula will return the desired result

Regards
Bosco

Dear Bosco,

Thanks again.