Excel: Count Unique Values with Critera

1
votes

I am trying to write a formula that will count unique values (both text and numbers) in a range that also has blank fields with a set of criteria.

Criteria:

  1. Shipment Type = Specific Transport Type
  2. Status Column = "Unplanned"
  3. Despatch Month = Specific Month

This is very easy to execute with a pivot table. However I want to be able to write a formula instead, as I don't like the idea of having to refresh a pivot table every time new data is added.

Here is the formula I have written for Unplanned Shipping by Cost for Truck 2018-03 in cell I15:

=SUMIFS($F:$F,$C:$C,I$14,$A:$A,$H15,$B:$B,"Unplanned")

The same logic doesn't really apply when writing a =COUNTIFS formula to measure unique consignments as the unique values with blanks fields adds a layer of complexity.

See Image

1
exceluniquedistinctformulacriteria

1 Answers

1
votes

to count the number of record where D:D is not blank:

write in cell I7 =countIFS($D:$D,"<>"&"",$C:$C,I$6,$A:$A,$H7,$B:$B,"Unplanned")