Formula to Count Unique Distinct Values That Meet Multiple Criteria - Phase 2

[Gomesy]

I'm working on a project where I need to count unique distinct values that meet more multiple criteria. In this instance I need to look at:

1. Orders that were placed in 2014
2. Unique customers that placed Web Orders

I am trying to determine how many units (rooms) each unique customer represents. I am attaching some test data for your use:

Year	Web Orders	HDS Account #	Units
2014		12560316	80
2014		12560316	80
2014		4887227	79
2014		4887227	79
2014	Web Orders	1939643	94
2014	Web Orders	1939643	94
2014	Web Orders	1939643	94
2014	Web Orders	1939643	94
2014	Web Orders	1939643	94
2014		5909972	112
2014		5909972	112
2014		2068909	86
2015		3329331	83
2015		3329331	83
2015		3329331	83
2015		3329331	83
2015		3329331	83
2015	Web Orders	6129968	85
2015	Web Orders	6129968	85
2015		6151214	115
2015		6151214	115
2015		6151214	115
2015		6147656	83
2015		6147656	83
2016		2066665	68
2016		2066665	68
2016		2066665	68
2016	Web Orders	14148293	106
2016	Web Orders	14148293	106
2016	Web Orders	14148293	106
2016	Web Orders	14148293	106
2016	Web Orders	14148293	106
2016		14354110	78
2016		14354110	78
2016		14354110	78
2016		14354110	78
2017	Web Orders	14148293	106
2017	Web Orders	14148293	106
2017	Web Orders	14148293	106
2017	Web Orders	14148293	106
2017	Web Orders	14148293	106
2017	Web Orders	14148293	106
2017		12560316	80
2017		12560316	80
2017		12560316	80
2017		12560316	80
2017		12560316	80

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

As you can see in the above test data, only customer 1939643 placed any Web Orders in 2014 and therefore the corresponding number of units (# of rooms) for this customer is 94 (even though this customer appears on 5 total rows containing Web Orders in 2014).

Does this make sense? Can someone write me an array formula that will work please?

Thank you,

Rick

 

[ttray33y]

The COUNTIFS function? Assuming data are at A:D & starts @ row 2 with row 1 as headers, then try below.

=COUNTIFS(A2:A999,"2014",B2:B999,"[COLOR=#333333]Web Orders[/COLOR]")

modify as per your requirement.

 

[Gomesy]

Not exactly what I'm looking for. Your formula doesn't consider the account number. The database I'm working in has over 25K rows. There are many instances where the account numbers are different during a particular year yet they have the same number of units. I'm trying to only SUM the units for the account numbers that have placed WEB ORDERS but I only want to SUM those units ONCE during a particular year.

I don't want to count the instances. I want to SUM the units for the UNIQUE accounts that placed WEB ORDERS during 2014.

 

[ttray33y]

Not exactly what I'm looking for. Your formula doesn't consider the account number. The database I'm working in has over 25K rows. There are many instances where the account numbers are different during a particular year yet they have the same number of units. I'm trying to only SUM the units for the account numbers that have placed WEB ORDERS but I only want to SUM those units ONCE during a particular year.

I don't want to count the instances. I want to SUM the units for the UNIQUE accounts that placed WEB ORDERS during 2014.

because you only stated 2 criteria s on your originals, you can always add an additional criteria for countifs.

 

[Gomesy]

It still doesn't work. Finding the year 2014 and the Web Orders is relatively easy. Once you try to isolate the unique accounts and their corresponding units is when you run into problems.


Sorry.

 

[PaddyD]

"I want to SUM the units for the UNIQUE accounts that placed WEB ORDERS during 2014."

Might just be me after a long week, but I am confused.

Unless you are also counting the uniques (or doing something with them):

SUM the units for the UNIQUE accounts that placed WEB ORDERS during 2014

Is the same as:

SUM the units for that placed WEB ORDERS during 2014

...unless you mean something a bit different by unique. Perhaps it's 'customers who only (or uniquely) placed web orders in 2014'??

Anyway - can you step us through an simplified example where the uniqueness makes a difference...

 

[PaddyD]

Or perhaps it's just a pivot table - by year, web order, customer then sum the rooms??

 

[Aladin Akyurek]

See: https://www.mrexcel.com/forum/excel...alues-meet-multiple-criteria.html#post4886960

 

[Gomesy]

If you take a look at the dataset I originally included, you'll see that Account # 1939643 was the only account that placed any Web Orders in 2014. I only need to count the number of units once (94 units) even though this account is listed as having made 5 Web Orders. I am using this data to determine how much spend per unit was made on accounts that placed Web Orders. If I use your formula, I run the risk of overstating the number of units (potentially 470 units) and therefore understating the spend per unit. I can't use a Pivot Table because it will SUM all of the units for 1939643 in 2014. I only need to see it once. I can't do a count of units in a Pivot Table either because it will return the value of 1 when I need it to return the value of 94 for 2014.