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:

[TABLE="width: 234"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Year[/TD]
[TD]Web Orders[/TD]
[TD]HDS Account #[/TD]
[TD]Units[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD] 80[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD] 80[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]4887227[/TD]
[TD] 79[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]4887227 [/TD]
[TD] 79[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD] 94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD] 94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD] 94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD] 94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD]Web Orders[/TD]
[TD]1939643[/TD]
[TD] 94[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]5909972[/TD]
[TD] 112[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]5909972[/TD]
[TD] 112[/TD]
[/TR]
[TR]
[TD]2014[/TD]
[TD][/TD]
[TD]2068909[/TD]
[TD] 86[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD] 83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD] 83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD] 83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD] 83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]3329331[/TD]
[TD] 83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Web Orders[/TD]
[TD]6129968[/TD]
[TD] 85[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD]Web Orders[/TD]
[TD]6129968[/TD]
[TD] 85[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6151214[/TD]
[TD] 115[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6151214[/TD]
[TD] 115[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6151214[/TD]
[TD] 115[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6147656[/TD]
[TD] 83[/TD]
[/TR]
[TR]
[TD]2015[/TD]
[TD][/TD]
[TD]6147656[/TD]
[TD] 83[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]2066665[/TD]
[TD] 68[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]2066665[/TD]
[TD] 68[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]2066665[/TD]
[TD] 68[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD] 106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD] 106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD] 106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD] 106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD] 106[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD] 78[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD] 78[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD] 78[/TD]
[/TR]
[TR]
[TD]2016[/TD]
[TD][/TD]
[TD]14354110[/TD]
[TD] 78[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD] 106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293 [/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD]Web Orders[/TD]
[TD]14148293[/TD]
[TD]106[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]2017[/TD]
[TD][/TD]
[TD]12560316[/TD]
[TD]80[/TD]
[/TR]
</tbody>[/TABLE]

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.