I have a set of data similar to the table below. I want to be able to filter by name(s) (I'm currently using the filter tab), calculate the total invoices assigned to that name(s) (I have used a subtotal formula), and then sum only the paid invoices assigned to that name(s) (this is the formula I can't work out). I want to do this without having to apply a further filter to the Paid(Yes/No) column as I want to still display unpaid invoices. My current workaround is a 4th hidden column containing an IF formula but this isn't very elegant. Any solutions? Thanks

Hi Jmiall,

Not quite sure if this is what you are looking for, but using a Insert Pivot with below settings gives the desired results I suppose.

Row:Name

Column:Paid

Values: Invoice

Only catch being your data is not dynamically updated, you need to generate the pivot everytime. If the output format servers your purpose we can think up of something else which would replicate your requirement.

How to create a dynamic pivot table and refresh automatically in exc