0

I'm struggling to sum the right values. It looked quite easy at first, but the more it try it the more complicated it seems to be. An example of my data is in an image below.
My data consists of sellinglots from an auction company, in the example there are 3 different auctions (auction01, 02 and 03) in the same table. Column B contains the LotID, D = quantity that I try to sum, E = customerID and F = status (sold or unsold).

enter image description here

What I try to do is sum the unsold quantity with the following criteria:
- qty per CustomerID
- only distinct LotID
- only unsold qty after all auctions. (e.g. lotID 900002 and 900005 are never sold, while 900013 is unsold at auction02 but sold at auction03 so I don't want to sum it.)

I got quite close, but I can't seem to get the last criteria implemented.
The results I want are in Cells J14 and J15, my 2 attempts for customer A are in cell I20 and I21. 

Attempt 1:  
=SUM(IF(FREQUENCY(IF(Table1[CustomerID]=H14;IF(Table1[Status]=J13;MATCH(Table1[LotID];Table1[LotID];0)));ROW(Table1[Qty])-ROW($D$2)+1)>0;Table1[Qty]))

Attempt 2: 
=SUMPRODUCT(IFERROR((Table1[Status]&Table1[CustomerID]=J13&H14)/COUNTIFS(Table1[LotID];Table1[LotID];Table1[Status];J13;Table1[CustomerID];H14);0);Table1[Qty])
Improve this question
6
  • (1) Please don’t post images of text.  Post a textual representation of your data, as was done here, here, here, here, here and here; use the Format Text as Table or the Plain Text Tables generator site if you want. … (Cont’d)
    – Scott - Слава Україні
    May 27, 2019 at 22:13
  • (Cont’d) …  (2) It might help if you described your data a little better.  For example, “Data are sorted by auction; all the rows for an auction are together. A lot (“LotID”) is associated with a customer, and can appear in multiple auctions until it is sold.”  If “SalesLotID” is irrelevant to the question, can you leave it out?  For that matter, do we really need to see the Auction ID?  (3) Include the output data you expect from your input data (clearly identifying how it is computed).  For example, if you want to get 675 as the unsold total for Customer B, … (Cont’d)
    – Scott - Слава Україні
    May 27, 2019 at 22:13
  • (Cont’d) …  explain that it is 200+225+250 (D3+D9+D19).  (4) Don’t clutter your data with irrelevancies.  For example, you say you want to sum the unsold quantity, but you show 30 rows of data, only 9 of which are unsold.  Include a few “sold” rows as filler, and enough to illustrate your third objective; but 21 seems like too many.  And, while it’s good to show us the results you get from the formulas you tried, that’s not nearly as important as knowing what results you want.  (And I have no idea what you’re showing us in H11:K16.)
    – Scott - Слава Україні
    May 27, 2019 at 22:13
  • @Greg Viers: “enter image description here” is meant to be a placeholder for an image description. An example image description would be “auction data spreadsheet”. If you don’t want to compose an image description, that’s OK (although we’d rather that you did, but thanks for editing the question to embed the image), but there’s really no point in re-inserting the “enter image description here” placeholder.
    – Scott - Слава Україні
    May 28, 2019 at 2:28
  • 1
    @Scott: Thanks for your comments I'll be more careful and specific when writing a new post.
    – Mark
    May 29, 2019 at 10:55

1 Answer 1

Reset to default
0

As shown in the picture below, add a new column G that counts the number of occurrences of a given "LotID" (array formula) and a new column H that is a binary that indicates whether the item was ultimately sold (value of "1") or never sold (value of "0") (array formula). With these columns, you should be able to solve your problem with the array formulas next to "A" and "B".

If this solves your problem, please mark it as an answer. If not, please provide details as to where this solution falls short.

enter image description here

Formulas:

G2: =SUM(IF([LotID]=B2,1))

H2: =SUM(IF(([LotID]=B2)*([Status]="sold"),1,0))

"A": =SUM(IF((Table1[CustomerID]=D9)*(Table1[Status]="unsold")*(Table1[Ultimately Sold]=0),Table1[Qty]/Table1[LotID Total]))

"B": =SUM(IF((Table1[CustomerID]=D10)*(Table1[Status]="unsold")*(Table1[Ultimately Sold]=0),Table1[Qty]/Table1[LotID Total]))

Improve this answer
1
  • Thanks for this solution. Your 'Ultimately sold' column solved my problem. I've added this column to my table and replaced the 'Table1[Status]' part with it in both of my attempts and I get the result I need!
    – Mark
    May 29, 2019 at 10:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .