Shifting "table" to desire range and sumif by last Digit.

[jp16]

Currently, the clerk in my company will key in the "Gain" in the respective cost centre column. We have 5 Brand and each brand is split to 3 department. The Cost Centre is 4 digit. The first 2 digit represent the Brand while the last 2 digit represent the department (Sales, Aftersales, Service).

HOwever, we have 5 Brand * 3 Department which is 15 Cost Centre. Refer to Tab "Data Entry". After the Clerk input the "Data Entry". Another person will add in a "Summary" at tab "Current Report". U can see that the formula is quite Manual. After which he will print and show it to the boss.

HOwever, as you can see, not all the 15 Cost centre will have figure. Normally we only use 3-4 of them and the rest is all Blank.

Hence i come up with a way, see "Propose Report". HOwever, i discover a problem. I am not able to Shift the Summary to the end of the Data. And aso. i don't know how to SUmif by last Digit.

Anybody can help?

[JohnTopley]

Confused by how you determine the Cost Centre with many blank columns.

Try

in Q2 and copy across

=SUMPRODUCT(($B$2:$N$10)*(RIGHT($B$1:$N$1,2)=VLOOKUP(Q1,'Formula to get Cost Centre'!$A$1:$B$10,2,0)))

where columns A:B in "Formula to get cost centre" have the following:


Sales 01
Aftersales 02
Service 03

The 01 etc are TEXT entries

This will give TOTAL for each department: extend range as required

[jp16]

Confused by how you determine the Cost Centre with many blank columns.

Try

in Q2 and copy across

=SUMPRODUCT(($B$2:$N$10)*(RIGHT($B$1:$N$1,2)=VLOOKUP(Q1,'Formula to get Cost Centre'!$A$1:$B$10,2,0)))

where columns A:B in "Formula to get cost centre" have the following:


Sales 01
Aftersales 02
Service 03

The 01 etc are TEXT entries

This will give TOTAL for each department: extend range as required

The cost centre i show is ALL the cost centre that we have. However, some cost centre is quite inactive, n some have transaction maybe once 1 year.
Hence there is many blank in the row.

Anyway, who know how to Sumif by last digit of the cost centre?

[JohnTopley]

The formula give sums by department.