Getting Colour wise totals referring to a "Colour Code"

[Anuru]

Hi...

Please kindle see the attached data table. How can I get the cumulative totals of the input, out put details referring to the "colour code" ??

[dominicb]

Good afternoon Anuru

You can only do this using macros.
See attached.

HTH

DominicB

[Anuru]

Thanks a lot, can you please kindly tell me, what is 7, 3 and 23 in the formula. If I wan to add more colours, how can I expand this formula?How can I copy this formula to my original work sheet? Will the macro work then?

Good afternoon Anuru

You can only do this using macros.
See attached.

HTH

DominicB

[Saarang84]

You can even use sheet formulas. I would go with SUMIFS function. Refer workbook attached, cells with formulas are colored in yellow.

Note: The values you input initially were wrong.

[Anuru]

Hi Sarang,

Million thanks for the solution. However, there is one more thing, that is..the data in the rows 4,5,6 etc cannot be predefined, as the data in these columns are kept adding every day, so, I want the cumulative of these columns to be kept adding to the summary of colour totals table when these data occur daily..Is there any solution to that??

[Anuru]

Hi Sarang,

Please see the attached actual document of the above related work out. In this, there is a formula in D10(highlighted in red) which I got from the Excel Forum. It calculates the totals in columns when they occur..can we get the concept of this formula for the above colour wise work out??

[Anuru]

Hi sarang, here is the attachment...

[Anuru]

Sorry, I don't know how to attache that document with a reply message..here is the formula...

=IFERROR(SUM($H$9 : INDEX($H$9:$AL$9, MATCH(10^100, $H$9:$AL$9))),0)

[Saarang84]

However, there is one more thing, that is..the data in the rows 4,5,6 etc cannot be predefined, as the data in these columns are kept adding every day, so, I want the cumulative of these columns to be kept adding to the summary of colour totals table when these data occur daily..Is there any solution to that??

You have Input in row 4, Output in row 5 and Polybag Out in row 6. You say you don't have the values predefined - are they calculated / populated via formulas? You also say want a cumulative sum. Do you want cumulative sum for each color code?

In that case, you need to give more data and depict what you actually want the output to be in the worksheet.

[Anuru]

The values in date columns will occur when the days are advancing, I am not able to say what will be tomorrow input, out put and poly bag etc, these values are collected from the production line and entered in to this work sheet on daily basis, and we follow up production status accordingly...Yes, I need the cumulative of colours wise values in "day columns"...

You have Input in row 4, Output in row 5 and Polybag Out in row 6. You say you don't have the values predefined - are they calculated / populated via formulas? You also say want a cumulative sum. Do you want cumulative sum for each color code?

In that case, you need to give more data and depict what you actually want the output to be in the worksheet.

[Saarang84]

The values in date columns will occur when the days are advancing, I am not able to say what will be tomorrow input, out put and poly bag etc, these values are collected from the production line and entered in to this work sheet on daily basis, and we follow up production status accordingly...Yes, I need the cumulative of colours wise values in "day columns"...

Hi Anuru,

Can u populate some dummy numbers in the sheet? It would be difficult to code a formula without numbers. Also, you clearly mention in what cells & rows you need to code formulas and give us the logic for the same.

Note : Kindly change the font type (like Arial), its difficult to read the text in your actual workbook.

[Anuru]

Hi Saarang,


Please kindly refer to the attached work book.

In this data sheet, the daily production values are typed on daily basis in the columns starting from M. As you can see, there are some indicators shown in the cells from D14 to D24.

What I need here is, I need to show the values in the cells from M14 to M24, N14 to N24, O14 to O24 etc to be shown in the cells from K14 to K24 based on the last date of update of the work book. For an example, K14 to K24 now shows the values in column O14 to O24(May 21st). Here I have just copied the details from O14 to O24 for the purpose of only showing you.

I need the values to be displayed automatically in column K14 to K24 based on the running date, when the days are advancing..

I started a new thread for this, but no body is posting a reply....

[Saarang84]

Hi Anuru,

Try this formula,

=OFFSET(K14,0,COUNTIF($M$14:$BI$14,">0")+1)

[Anuru]

Hi Saarang,

Thanks a lot for the formula. However, it doesn't work in K15,K16 and K17. These cells include a little bit complicated formulas of efficiency calculations,that may be the reason. Also, if there are blank columns, the formula doesn't seem to work. But, I just typed "1" in blank columns, then it works(except K15,K16 and K17).

Is there a further solution?

Updated work book is attached herewith for your reference...

[Saarang84]

In K14, use the below formula and copy down :

=OFFSET(K14,0,COUNTIF($M14:$BI14,">0")+1)

Also don't forget to match the formatting type of the cells accordingly (decimal places for numbers, percentages (%), etc.)

[Anuru]

Hi Experts,

Can somebody help me in the the above requirement....??

[Saarang84]

Hi Anuru,

Refer the updated workbook attached.

[Saarang84]

You need to remove the formula for the unpopulated cells