Count If with multiple criteria and unique values

[dixon1983]

Hi,
I dont know if this is possible or not but thought I'd ask anyway.

I have a sheet of data and want to run a count on the values in Column A with the following criteria:

- If the value in column B is repeated then the value in column A can only be counted once...
- Column C must equal 'M'.


Example:
A.....B.....C
1.....42....M
1.....42....M
2.....41....A
2.....41....M
1.....56....M


In this example 1 would be counted 2 times, and 2 would be counted once.

I have about 15 sheets of data like this and would like a master sheet to look into each sheet and give me a count of each so id prefer to not use the advanced filter => unique variables option if possible.

Any help would be greatly appreciated.

Dixon.

 

[DeltBunyan]

So this is an example of what I'm looking for - the Unique Contacts column on the right. I am agnostic about which of a series of contacts in the same month is considered "unique" - it can be first, last, or something in the middle, it really doesn't matter. For some reason the 0's were eliminated from the table, but wherever you see blank cells, those are 0's.

Does this make more sense?

Thanks!

 

[RoryA]

Like this?

Excel 2010
	A	B	C	D	E
1	Entity	Contact	Month	Associate	Unique Contact
2	339612939		5	Bar, J
3	339831335	1	6	Bar, J	1
4	339831335		6	Amo, M
5	339831335	1	6	Amo, M
6	339831335		6	Amo, M
7	339102126	1	4	Bar, J	1
8	339594067		4	Amo, M
9	339594067	1	6	Amo, M	1
10	339591235		5	Bar, J
11	339100199		4	Amo, M
12	339279390	1	1	Bar, J	1
13	339279390	1	1	Bar, J
14	339279390	1	4	Med, M	1
15	339138746	1	1	Bar, J	1
16	339459248	1	1	Mer, D	1
17	339459248	1	1	Mer, D
18	339459248		1	Mer, D
19	339459248		3	Mer, D
20	339459248	1	3	Zim, C	1
21	339459248	1	3	Zim, C
22	339459248		4	Amo, M
23	339459248		4	Amo, M
24	339459248	1	4	Amo, M	1
Sheet1

Cell Formulas
Range	Formula
E2	=IF(B2<>1,"",IF(COUNTIFS(A$1:A2,A2,B$1:B2,1,C$1:C2,C2)>1,"",1))

and copy E2 down.

 

[DeltBunyan]

Looks like it works perfectly! Thank you! If I change the order within columns, such as sorting by month or associate, rather than entity, will that affect the results in the Unique Contacts column?

 

[RoryA]

Only in that it flags the first contact in the column, so you may get 1s in different places.

 

[DeltBunyan]

Is there a way to control for that and avoid potentially putting 1's in the wrong places? By default I intend to sort by month (most recent to oldest).

 

[RoryA]

How would they be wrong? It would still be the first contact for that Associate / Month / Entity combination, it just might appear in a different row based on your sorting.

 

[DeltBunyan]

Ah ok, so then no worries! Thank you so much!

 

[RoryA]

Glad to help.

 

[ramana1983]

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Product[/TD]
[TD]Size[/TD]
[TD]result[/TD]
[/TR]
[TR]
[TD]configurable[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]simple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]simple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]configurable[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]simple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]simple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]simple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]simple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]configurable[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]simple[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]configurable[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Dear All,
this my problem at hand
i can use countif simple & return the value here
but i need is it should return the values between two texts in coloumn (i.e confifgurable)
it should count how many times the text simple is present between two configurable texts

regards
PVR Ramana

 

[Aladin Akyurek]

C2, copied down:

=IF(A2="configurable",IF(COUNTIFS($A$1:A2,A2)>1,
    COUNTIFS(INDEX($A$1:A1,LOOKUP(9.99999999999999E+307,1/($A$1:A1="configurable"),
    ROW($A$1:A1)-ROW($A$1)+1)):A2,"simple"),""),"")