sumif: criteria - helper column is the first occurence

I have an accounting spreadsheet in Excel and we currently tie purchases to a unique identifier (p1, p2, p3, etc) so that we can also include details on each individual item in the purchase. The relevant parts of the table are as follows:

Purchase ID | Project | item | total order price
p1 | office | stapler | $100
p2 | supplies | mop | $500
p3 | office | stapler | $200
p3 | office | tape | $200



As you can see, p3 (or purchase 3) has $200 listed twice, because it is the same purchase, but shows the two different items on that purchase.

I am having difficulty only pulling p3 once for this formula. I am also using an inserted table labeled "purchases" and like to reference columns by the column header name (ex: Purchases[Purchase ID]).


Basically, what I am trying to obtain is a formula that will pull the total money spent on the "office" project, my thoughts are to incorporate a formula that only use the first occurrence of the purchase ID. The formula should pull $300 from the above table (p1+p3).

Formula:

Please Login or Register  to view this content.

Which in plain English is sum all values in column C for the rows where column A is P3 and column B is Office, then divide that by the number of rows which meet those conditions.

That or a progressive count helper column and including that in the SUMIFS.

BSB

Maybe this?

Formula:

Please Login or Register  to view this content.

Adjust ranges to suit.

BSB

That is perfect BSB, and much simpler than I thought it would be thank you for your help!

Happy to help

BSB