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

[TABLE="width: 298"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Ticket no[/TD]
[TD]Date[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]TT-000030817681[/TD]
[TD="align: right"]12/30/2016[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]TT-000030817681[/TD]
[TD="align: right"]12/30/2016[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]TT-000030817681[/TD]
[TD="align: right"]12/30/2016[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]TT-000030819464[/TD]
[TD="align: right"]12/30/2016[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]TT-000030826520[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]93[/TD]
[/TR]
[TR]
[TD]TT-000030826520[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]TT-000030819811[/TD]
[TD="align: right"]12/30/2016[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]TT-000030827310[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]104[/TD]
[/TR]
[TR]
[TD]TT-000030811346[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]81[/TD]
[/TR]
[TR]
[TD]TT-000030826521[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]TT-000030826521[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]TT-000030826521[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD]TT-000030826521[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD]TT-000030826521[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]92[/TD]
[/TR]
[TR]
[TD]TT-000030826521[/TD]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]72[/TD]
[/TR]
</tbody>[/TABLE]

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

Something like this:

[TABLE="width: 242"]
<colgroup><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Total Amount[/TD]
[/TR]
[TR]
[TD]12/31/2016[/TD]
[TD]370[/TD]
[/TR]
[TR]
[TD]12/30/2016[/TD]
[TD]129[/TD]
[/TR]
</tbody>[/TABLE]

The working of the above result would be based on:
[TABLE="width: 363"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Ticket no[/TD]
[TD] Max of Amount[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]TT-000030811346[/TD]
[TD] 81[/TD]
[TD]12/31/2016[/TD]
[/TR]
[TR]
[TD]TT-000030817681[/TD]
[TD] 60[/TD]
[TD]12/30/2016[/TD]
[/TR]
[TR]
[TD]TT-000030819464[/TD]
[TD] 24[/TD]
[TD]12/30/2016[/TD]
[/TR]
[TR]
[TD]TT-000030819811[/TD]
[TD] 45[/TD]
[TD]12/30/2016[/TD]
[/TR]
[TR]
[TD]TT-000030826520[/TD]
[TD] 93[/TD]
[TD]12/31/2016[/TD]
[/TR]
[TR]
[TD]TT-000030826521[/TD]
[TD] 92[/TD]
[TD]12/31/2016[/TD]
[/TR]
[TR]
[TD]TT-000030827310[/TD]
[TD] 104[/TD]
[TD]12/31/2016

[/TD]
[/TR]
</tbody>[/TABLE]

Aladin Akyurek · Jan 12, 2017

@djawaz

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][td]

F

[/td][/tr][tr][td]

1

[/td][td]Ticket no[/td][td]Date[/td][td]Amount[/td][td][/td][td]Date[/td][td]Total Amount[/td][/tr]
[tr][td]

2

[/td][td]TT-000030817681[/td][td]

12/30/2016

[/td][td]

60

[/td][td][/td][td]

12/31/2016

[/td][td]

370

[/td][/tr]
[tr][td]

3

[/td][td]TT-000030817681[/td][td]

12/30/2016

[/td][td]

60

[/td][td][/td][td]

12/30/2016

[/td][td]

129

[/td][/tr]
[tr][td]

4

[/td][td]TT-000030817681[/td][td]

12/30/2016

[/td][td]

60