Count If with multiple criteria and unique values

dixon1983 · Apr 2, 2008

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.

Aladin Akyurek · Oct 27, 2016

yaniskyr said:
The method that evolved in this thread is amazing. May I bump the thread with a question though?

My personalised formula works ok until I add the wildcard in the last IF - $F5&"*". During trial and error, replacing that with $F5 and adjusting F5 to a precise value, it works. Any ideas??

Code:

=SUM(IF(FREQUENCY( IF(1-(Export!$B2:$B40000=""), IF(Export!$M2:$M40000>=G$2, IF(Export!$M2:$M40000<=J$2, IF(Export!$D2:$D40000=$B5, IF(Export!$F2:$F40000=$D5, [B]IF(Export!$K2:$K40000=$F5&"*"[/B], MATCH(Export!B2:B40000,Export!B2:B40000,0))))))),ROW(Export!B2:B40000)-ROW(Export!B2)+1),1))

Control+shift+enter, not just enter:

Rich (BB code):

=SUM(IF(FREQUENCY(
      IF(1-(Export!$B$2:$B$40000=""),
      IF(Export!$M$2:$M$40000>=G$2,
      IF(Export!$M$2:$M$40000<=J$2,
      IF(Export!$D$2:$D$40000=$B5,
      IF(Export!$F$2:$F$40000=$D5,
      IF(ISNUMBER(SEARCH("|"&$F5,"|"&Export!$K2:$K40000)),
      MATCH(Export!$B$2:$B$40000,Export!$B$2:$B$40000,0))))))),
       ROW(Export!$B$2:$B$40000)-ROW(Export!$B$2)+1),1))

Hope the speed of performance will be bearable.

yaniskyr · Oct 27, 2016

Aladin Akyurek said:
Hope the speed of performance will be bearable.

Holy crap, what's that "|" sorcery? Can you please explain what's happening?

It works, but I'll only know if it's bearable when the report extends to it's ultimate size. Which will be soon.

Aladin Akyurek · Oct 27, 2016

yaniskyr said:
Holy crap, what's that "|" sorcery? Can you please explain what's happening?

Searching for say da in a range consisting of

daque
ada
nada

where we only want to pick out daque, not ada or nada, we can better search:

|da

in:

|daque
|ada
|nada

It works, but I'll only know if it's bearable when the report extends to it's ultimate size. Which will be soon.

You might then want to consider other means like SQL for example.

djawaz · Jan 12, 2017

Hi,
Recently I ran into a problem where I unable to get the formula right. Need a helping hand.

I have following data

A	B	C
Ticket no	Date	Amount
TT-000030817681	12/30/2016	60
TT-000030817681	12/30/2016	60
TT-000030817681	12/30/2016	60
TT-000030819464	12/30/2016	24
TT-000030826520	12/31/2016	93
TT-000030826520	12/31/2016	77
TT-000030819811	12/30/2016	45
TT-000030827310	12/31/2016	104
TT-000030811346	12/31/2016	81
TT-000030826521	12/31/2016	15
TT-000030826521	12/31/2016	13
TT-000030826521	12/31/2016	17
TT-000030826521	12/31/2016	29
TT-000030826521	12/31/2016	92
TT-000030826521	12/31/2016	72

What I need is Max Value of Amount of Each unique ticket aggregated on date.

Something like this:

Date	Total Amount
12/31/2016	370
12/30/2016	129

The working of the above result would be based on:

Ticket no	Max of Amount	Date
TT-000030811346	81	12/31/2016
TT-000030817681	60	12/30/2016
TT-000030819464	24	12/30/2016
TT-000030819811	45	12/30/2016
TT-000030826520	93	12/31/2016
TT-000030826521	92	12/31/2016
TT-000030827310	104	12/31/2016

Aladin Akyurek · Jan 12, 2017

@djawaz

Row\Col	A	B	C	D	E	F
1	Ticket no	Date	Amount		Date	Total Amount
2	TT-000030817681	12/30/2016	60		12/31/2016	370
3	TT-000030817681	12/30/2016	60		12/30/2016	129
4	TT-000030817681	12/30/2016	60
5	TT-000030819464	12/30/2016	24
6	TT-000030826520	12/31/2016	93
7	TT-000030826520	12/31/2016	77
8	TT-000030819811	12/30/2016	45
9	TT-000030827310	12/31/2016	104
10	TT-000030811346	12/31/2016	81
11	TT-000030826521	12/31/2016	15
12	TT-000030826521	12/31/2016	13
13	TT-000030826521	12/31/2016	17
14	TT-000030826521	12/31/2016	29
15	TT-000030826521	12/31/2016	92
16	TT-000030826521	12/31/2016	72

A2:A16 is named as Ticket in Name Manager; B2:B16 as Date, and C2:C16 as Amount.

Define also Ivec in Name Manager as referring to:

=ROW(Ticket)-ROW(INDEX(Ticket,1,1))+1

In F2 control+shift+enter, not just enter, and copy down:

=SUM(MAXIFS(Amount,Ticket,IF(FREQUENCY(IF(1-(Ticket=""),IF(Date=E2,MATCH(Ticket,Ticket,0))),Ivec),Ticket),Date,E2))

MAXIFS requires at least a 2016 Excel version.

djawaz · Jan 12, 2017

Thank you for the reply. Unfortunately I am using Excel 2013...wont get a chance to try it out.

djawaz · Jan 13, 2017

Aladin Akyurek said:
@djawaz

Row\Col
A
B
C
D
E
F
1
Ticket no Date Amount Date Total Amount
2
TT-000030817681
12/30/2016
60
12/31/2016
370
3
TT-000030817681
12/30/2016
60
12/30/2016
129
4
TT-000030817681
12/30/2016
60
5
TT-000030819464
12/30/2016
24
6
TT-000030826520
12/31/2016
93
7
TT-000030826520
12/31/2016
77
8
TT-000030819811
12/30/2016
45
9
TT-000030827310
12/31/2016
104
10
TT-000030811346
12/31/2016
81
11
TT-000030826521
12/31/2016
15
12
TT-000030826521
12/31/2016
13
13
TT-000030826521
12/31/2016
17
14
TT-000030826521
12/31/2016
29
15
TT-000030826521
12/31/2016
92
16
TT-000030826521
12/31/2016
72

<tbody>
</tbody>

A2:A16 is named as Ticket in Name Manager; B2:B16 as Date, and C2:C16 as Amount.

Define also Ivec in Name Manager as referring to:

=ROW(Ticket)-ROW(INDEX(Ticket,1,1))+1

In F2 control+shift+enter, not just enter, and copy down:

=SUM(MAXIFS(Amount,Ticket,IF(FREQUENCY(IF(1-(Ticket=""),IF(Date=E2,MATCH(Ticket,Ticket,0))),Ivec),Ticket),Date,E2))

MAXIFS requires at least a 2016 Excel version.

Thank you Aladin. Your input was indeed quite helpful.
So here is the formula which can work in Excel 2013:

=SUM(IF(Date=E2,(MATCH(Ticket,IF(COUNTIFS(Ticket,Ticket,Amount,">"&Amount,Date,E2)=0,Ticket),0)=ROW(Ticket)-MIN(ROW(Ticket))+1)*Amount))

Aladin Akyurek · Jan 13, 2017

djawaz said:
Thank you Aladin. Your input was indeed quite helpful.
So here is the formula which can work in Excel 2013:

=SUM(IF(Date=E2,(MATCH(Ticket,IF(COUNTIFS(Ticket,Ticket,Amount,">"&Amount,Date,E2)=0,Ticket),0)=ROW(Ticket)-MIN(ROW(Ticket))+1)*Amount))

You are welcome. You could have used Ivec too:

{=SUM(IF(Date=E2,IF(MATCH(Ticket,IF(COUNTIFS(Ticket,Ticket,Amount,">"&Amount,Date,E2)=0,Ticket),0)=Ivec,Amount)))}

Orange666 · Feb 22, 2017

Hi - I tried figuring my problem, very similar to this thread,hoping someone can help out.

Please look table below:

Column
A B C D E F

BOLAS R F	ANN	20/01/2016	08/02/2016	38.22	Division 1
BOLAS R F	ANN	20/01/2016	08/02/2016	57.33	Division 1
BOLAS R F	ANN	09/08/2016	09/08/2016	7.35	Division 1
CCLES L	PER	27/05/2016	27/05/2016	7.35	Division 1
CCLES L	ANN	22/01/2016	27/01/2016	22.05	Division 1
CCLES L	ANN	01/03/2016	01/03/2016	7.35	Division 1
CCLES L	ANN	15/03/2016	18/03/2016	29.40	Division 1
CCLES L	PER	29/01/2016	29/01/2016	7.35	Division 1
CCLES L	PER	17/02/2016	17/02/2016	7.35	Division 1
CCLES L	PER	31/03/2016	01/04/2016	14.70	Division 1
KORD R A	ANN	08/08/2016	19/08/2016	18.38	Division 1
KORD R A	ANN	08/08/2016	19/08/2016	55.13	Division 1
KORD R A	ANN	13/12/2016	14/12/2016	14.70	Division 1
KORD R A	PER	09/09/2016	09/09/2016	7.35	Division 1
KORD R A	PER	30/11/2016	01/12/2016	7.35	Division 2
KORD R A	PER	30/11/2016	01/12/2016	7.35	Division 2
KORD R A	PER	05/12/2016	06/12/2016	14.70	Division 2
KORD R A	PER	15/04/2016	15/04/2016	7.35	Division 2
BOLAS R F	ANN	14/06/2016	14/06/2016	7.35	Division 2
ECCLES J L	ANN	30/05/2016	30/05/2016	7.35	Division 2
ECCLES J L	PER	05/05/2016	06/05/2016	14.70	Division 2
FORD R A	ANN	15/12/2016	16/12/2016	14.70	Division 3
FORD R A	ANN	03/01/2017	13/01/2017	54.12	Division 3
FORD R A	ANN	03/01/2017	13/01/2017	12.03	Division 3
FORD R A	PER	25/02/2016	26/02/2016	14.70	Division 3
BINK P H	PER	05/12/2016	05/12/2016	7.35	Division 3
SUTT M	PER	22/02/2016	26/02/2016	22.05	Division 3
SUTT M	PER	22/02/2016	26/02/2016	14.70	Division 3
SUTT M	PER	02/03/2016	02/03/2016	7.35	Division 3
SUTT M	PER	16/03/2016	16/03/2016	7.35	Division 3
SUTT M	PER	23/03/2016	23/03/2016	7.35	Division 4
SUTT M	PER	13/04/2016	13/04/2016	7.35	Division 4
SUTT M	PER	20/04/2016	20/04/2016	7.35	Division 4
SUTT M	PER	04/05/2016	04/05/2016	7.35	Division 4
SUTT M	PER	16/05/2016	16/05/2016	7.35	Division 4
SUTT M	PER	23/05/2016	23/05/2016	7.35	Division 4
SON M	ANN	29/03/2016	11/04/2016	37.80	Division 4
SON M	ANN	29/03/2016	11/04/2016	21.00	Division 4
SON M	ANN	12/04/2016	20/04/2016	44.40	Division 4
SON M	ANN	21/04/2016	21/04/2016	6.90	Division 4
SON M	PER	04/01/2016	04/01/2016	7.50	Division 4
VIAN R	ANN	12/07/2016	18/07/2016	10.50	Division 4
VIAN R	ANN	12/07/2016	18/07/2016	26.25	Division 4
VIAN R	PER	05/07/2016	06/07/2016	14.70	Division 4
VIAN R	ANN	13/05/2016	13/05/2016	2.85	Division 4
VIAN R	PER	04/02/2016	04/02/2016	7.35	Division 4
VIAN R	PER	01/03/2016	01/03/2016	2.85	Division 4
VIAN R	PER	11/05/2016	11/05/2016	1.50	Division 4
VIAN R	PER	02/06/2016	03/06/2016	14.70	Division 4

What I'm trying to count is: the average number of hrs (Column E) for specific type of leave (ANN or PER), between range of dates , for particular division, and total average for whole divisions.
Hope it's clear.
Thanks for your help.

Aladin Akyurek · Feb 22, 2017

Orange666 said:
[...]
What I'm trying to count is: the average number of hrs (Column E) for specific type of leave (ANN or PER), between range of dates , for particular division, and total average for whole divisions.
[...]

Row\Col	A	B	C	D	E	F	G	H
1
2	BOLAS R F	ANN	1/20/2016	2/8/2016	38.22	Division 1		ANN
3	BOLAS R F	ANN	1/20/2016	2/8/2016	57.33	Division 1		PER
4	BOLAS R F	ANN	8/9/2016	8/9/2016	7.35	Division 1		1/20/2016
5	CCLES L	PER	5/27/2016	5/27/2016	7.35	Division 1		3/1/2016
6	CCLES L	ANN	1/22/2016	1/27/2016	22.05	Division 1		division 1
7	CCLES L	ANN	3/1/2016	3/1/2016	7.35	Division 1		23.275
8	CCLES L	ANN	3/15/2016	3/18/2016	29.4	Division 1
9	CCLES L	PER	1/29/2016	1/29/2016	7.35	Division 1
10	CCLES L	PER	2/17/2016	2/17/2016	7.35	Division 1
11	CCLES L	PER	3/31/2016	4/1/2016	14.7	Division 1

In H7 control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(MATCH($B$2:$B$50,$H$2:$H$3,0)),IF($C$2:$C$50>=H4,IF($D$2:$D$50<=H5,IF($F$2:$F$50=H6,$E$2:$E$50)))))

This is a multiconditional average of hours where one of the conditions is "division 1", a particular division. Care to clarify what you mean by "total average for whole divisions"?

Count If with multiple criteria and unique values

dixon1983

Board Regular

Aladin Akyurek

MrExcel MVP

Excel Facts

yaniskyr

New Member

Aladin Akyurek

MrExcel MVP

djawaz

New Member

Aladin Akyurek

MrExcel MVP

djawaz

New Member

djawaz

New Member

Aladin Akyurek

MrExcel MVP

Orange666

New Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Count If with multiple criteria and unique values

Board Regular

MrExcel MVP

Excel Facts

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock