Counting Distinct with multiple ranges

Lynn Strahm

Hi All,

Thanks!

Jeffrey Anderson

@Lynn Strahm

Could you please provide a bit more information? What is the rule to use Drawing # vs Quote #? What defines distinct?

One way to possibly resolve is by adding a column that concatenates the fields (=[Drawing Number]@row + "_" + [Drawing Revision]@row + "_" + [Quote Number]@row + "_" + [Quote Revision]@row)

Depending on your rules, you may need to add a concatenated column for Drawing and Quote. Either way, the string value will be unique and you can count apply a count formula!

Let me know if you have any questions!

Respectfully,

Jeff

L_123

=count(distinct(collect([site type]:[site type],[drawing number]:[drawing number],"DW0002",[Quote Revision]:[Quote Revision],"A"

will return all unique values of site type where the drawing number is DW0002 and the quote revision is A

Lynn Strahm

Thanks for the responses! What I'm trying to do is write a formula that will tell me how many distinct requests there were by site type. For example, based on the data above, how many distinct "New Equipment" requests are on the list? A specific site may have multiple requests (drawings, quotes or both and revisions) but I want to count that site just once. Below is how the count works out for New Equipment based on the data above:

DW0001 A, QT0005 A = 1 count

DW0002 A, QT0006 A & DW0002 B & DW0002 C = 1 count

QT0003 A & QT0003 B = 1 count

DW0011 A = 1 count

DW0013 A, QT0019A = 1 count

QT0021 A = 1 count

Total count for New Equipment requests = 6

Thanks for your help!

Lynn Strahm

The above example is the long hand of what I'm trying to write a formula to accomplish. Does anyone have any additional thoughts on how to write a formula to accomplish this? Thanks!

L_123

Helper column with an if statement with the result being the number you want associated with the values, then a sumif.

or if you don't want to use that you can use stacked countif statements with mulipliers.

Apologies, i missed that you had responded to this.

Lynn Strahm

@L@123

I'm struggling to see how a helper column or stacked countif statements will accomplish what I'm trying to do. Maybe example formulas would help me. I thought about a helper column where I join the drawing and quote numbers and then count the distinct in the helper column. That wouldn't cover scenarios where a request included just a quote or drawing and not both. The challenge is writing a formula that recognizes which drawing numbers and quote numbers are for the same same site and counting all of them just once.