Counting Unique Items in a column based on a flag in a different collumn

English
#1

Data in the spreadsheet contains three columns of interest, “vendor”, “part number” and “status.” Each vendor can have multiple associated part numbers. The status column indicates warehouse status, i.,e “on hand”, “on order”, and “out.” I want to be able to count the number of vendors with a specified status. I can find the result using this formula: =SUM(1/COUNTIF($Trial.B2:B460,$Trial.B2:B460)) if I sort the spreadsheet by status and then enter edit the beginning and ending row number based on sort. I’ve tried variations using COUNTIFS and SUMPRODUCT without success, since I’m new to CALC maybe it’s a syntax error I haven’t figured out.

thanks

#2

Please explain:

  • Howcan OnHand/OnOrder/Out be values of WarehouseStatus? I would read that as values of StockItemStatus.
  • How can a Vendor have such a status? What’s the difference between Warehouse and Vendor?
  • Is an Item 1:1 designated by a PartNumber?
    Do you want (e.g.) to count the vendors with value Out for at least one of the Articles they are listed for?
    Please try to use exactly one term for one kind pof entity in such a case.
    Best prepare a simplified, but sfficiently ample sheet showing what you have and what you want, and attach it to your question by editing.
#3

There is a row for each part number, each row contains part number, vendor, and status columns. A particular vendor may have 100’s of part numbers. I want to count the number of vendors with a value of “out” for at least one part number.

Thanks

#5
  • What about the term Warehouse?
  • You gave a formula with references depending on your SheetDesign. I neither can reasonably criticize it nor omprove it without knowing more.
  • Do you excpect me to create a working example? Or should I be entitled to expect you to provide one? From my point of view the answer is clear insofar.
#4

Hi, have a look at the attached, I have add a column C=Count and populated 1 in each row, then used a Pivot table to produce a summary by vendor and status. Then a COUNTIF on each status below the table.
Let us know if it helps. Produced using windows 10 home, LO 7.2.5.2.
LOQ22020122.ods (18.0 KB)

#6

Thanks, this looks like it will do the trick.

#7

@CaptPat, please mark @gregors15’s answer as the solution so contributors do not spend more time on your question? Thanks.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.