Related Posts
What benefit you love most at your firm?
Conversation Starter
Spot the difference 👀👀
Any women doing GORUCK ?
Pro
Bowl Leader
Bowl Leader
Additional Posts in Excel Genius
Still don't have xlookup. #fomo
Visual Storyteller
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
And use that additional column (maybe ‘Delivered’ = Y/N) to conditionally format the Quantity column to auto highlight and then a Sumif.. remember to collapse the column if needed
You can use Subtotal to sorta do it the way you are thinking though! Filter out the unhighlighted rows to see the correct total
There is no native excel function that pulls the color format from a cell, so the answer is no*
*however, you can create your own vba code that identifies the color of a cell, and then sum all cells that match your "highlight criteria"
Not sure but here’s a work around. Rather than highlight, have a column for “include.” Then you can =IF(include cell = “yes”,total Q delivered,””)
Sumifs would be cleaner
You can filter by color
+1 to the suggestion above. I would just have a separate column that indicates “Delivered” or “Yes” and then have conditional formatting applied to the other columns based on the “Delivered” columns value. And then you can do a sumifs based on the “Delivered” column value.
Sumif
You may find greater scope for analysis if you include a delivery date instead of just a yes/no. Although perhaps overkill for your use case right now, if you did want to build in something later it's already there.
Either way, sumif/s is your friend here as others have said.
You can create custom function using VBA to do this I believe
This is what did it. I had to do some research on the VBA formula, but I was able to do SUMBYCOLOR once I had it.