Distinct Count With Multiple Qualifiers

[Ohioste]

First time posting over in the Office thread. I'm a Crystal guy through and through, but the company is moving directions with reporting and dragging me with them!

I've written a SQL refreshable query in a tab called "Data Pull". Within this tab, there are multiple reports be refreshed.

I have two reports that I've linked together with a vlookup.

One is an order information report and the other is a location of that order. An order for us could be a tote or carton. I want to be able to report how many "vessels"(totes and cartons) are in a given place at the time of refresh. For a tote, there could be more than one carton in the order information report that I spoke above about.

The information will look like this:

Tote # MHE Translated
Tote 1 TWR1EXT2
Tote 1 TWR1EXT2
Tote 1 TWR1EXT2
Tote 2 Location B
Tote 2 Location B
Carton 1 TWR1EXT2

My formula looks like: =SUM(IF("TWR1EXT2"=Table_ExternalData_4[MHE Translated], 1/(COUNTIFS(Table_ExternalData_4[MHE Translated], "TWR1EXT2",Table_ExternalData_4[TOTE_'#],Table_ExternalData_4[TOTE_'#])), 0))

It is an array.

The end result should be 2. It could be counting tote 1 and carton 1.

Any ideas?

Thanks in advance.

 

[SkipVought]

hi,

I would use MS Query to return the Distinct count of these two columns'

 

[SkipVought]

You might also check thread68-1749025 to see a discussion regarding a failed/or not, spreadsheet solution.