Calculating totals with hidden cells in Excel

Posted on

QUESTION :

I have a problem with my Excel sheet.

“Kolom4” has the product codes. Each code has two rows with data. With each code I want to have the combined values of “kolom11” till “kolom14” from both rows.

So for example for product code 10303814140001 I want the values
0 (0+0), 13 (10+3), 0 (0+0) and 13 (10+3)

See:

Example from document

Wanted result example

The problem is there are hidden cells between these values and I don’t know how I can make a formula which can calculate them together without the hidden cells and with which I can pull down to do it for the entire data file.

ANSWER :

You can get a total of those columns by creating a Pivot Table for “Kolum4” values. Pivot table will include hidden cells for calculations as well. Create a Pivot Table in Excel and add “Kolom4” as the field in “Rows” and “Kolom11” through “Kolom14” as the columns for “Values”. Default pivot table value function is to SUM values for each row matching “Kolom11” so this will give you the total.

Image from sample Excel worksheet

Leave a Reply

Your email address will not be published. Required fields are marked *