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])
Greg Viers
  • 192
Mark
  • 3

1 Answers1

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]))

Brian
  • 1,085