How to Extract Subtotal values when filter by color applied|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi Andy,

Excel doesn't work like that. It's not able to detect cell fill colours with formulas. In the attached file I've used INDEX & MATCH to lookup the values for the codes in column C. The total is simply a sum of the two values. 

Maybe this is close to what you're after, but if not, please provide another workbook with a more complete example of your data so we can understand the full picture.

Mynda

Hi Andy,

Like I said, Excel can't detect cell fill colour and use that as a way to extract data...unless it's in a PivotTable and the format of your data doesn't currently support that, or you have the skills to write some complex VBA, so let's try and find an easier way to give you what you want. 

First, I need to understand how the fill colour is being determined. If we can understand this logic then I can help you with a formula or PivotTable that replicates this logic. what's not clear from your example is you say you have 1500 rows, so is it possible there will be multiple results for each fill colour, or only ever one line for each i.e. fill colour is applied to each code contained in the column D and there are no duplicates.

Can you please provide a larger example that clearly illustrates whether you have duplicates and explain the logic behind how the fill colour is being applied. Also please advise what version of Excel you're using so we know what tools you have available to you.

Mynda

Hi Andy,

Thanks for sharing your file, however I cannot access it as I don't have permission.

The reason you aren't finding the answers in Google is because your approach is not in line with how Excel works. You need to take a step back and stop thinking of filtering taking place based on a colour, because before you could decide what colours to apply you had to have some logic around how to assign those colours, and it's that logic that we need to understand in order to help you.

You hinted at it in so far as saying there are specific names in column G that are filtered when you choose light grey etc., but we'd need to know that logic in order to use it to give you what you want. i.e. what are the list of names and how do they map to the colours.

That said, what I can see of your file, I can tell that it's not in the correct tabular layout that will help you achieve this easily using the built in tools available in Excel. If it were in a tabular layout, you could create a PivotTable from the data and use Slicers to filter the data based on your codes in column G and have that PivotTable automatically create a Pivot chart that updates upon new selections in the Slicer. Sounds complicated, but it's actually very easy.

Personally, I recommend you use Power Query to unpivot your data and load this unpivoted copy to another sheet, or direct to a PivotTable if you have Microsoft 365, and use Slicers to allow your users to automatically filter the data based on the codes.

I hope that points you in the right direction. If you don't like that idea, then you'll have to write some VBA code to do what you want.

Mynda

Hi Andy,

Please start a new thread in the VBA forum. Note that there is no guarantee you'll get someone to do this for you as the forum isn't a free consulting service, it's a support service designed to help you if you get stuck trying to create the VBA yourself. 

Mynda