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.

 

[a76marine]

Aladin, after reading this long thread I do believe you may be superhuman.

Can you possibly please help me as well? I'm working on a similar problem to many in this thread, however when I try to make it work I'm only getting 0's for the unique counts.

Here is what I am trying to fill out in cell B2:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Month[/TD]
[TD]2014[/TD]
[TD]2015[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]January[/TD]
[TD]???[/TD]
[TD][/TD]
[TD]Team A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]February[/TD]
[TD][/TD]
[TD][/TD]
[TD]Team A[/TD]
[/TR]
</tbody>[/TABLE]

On a separate sheet named Data I have this raw data that I am trying to count unique customers by team, by year, and by month:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Data A[/TD]
[TD]Data B[/TD]
[TD]Data C[/TD]
[TD]Data D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Customer[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[TD]Team[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]January[/TD]
[TD]2014[/TD]
[TD]Team A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Peach[/TD]
[TD]February[/TD]
[TD]2014[/TD]
[TD]Team A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Apple[/TD]
[TD]January[/TD]
[TD]2014[/TD]
[TD]Team A[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]January[/TD]
[TD]2015[/TD]
[TD]Team B[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Orange[/TD]
[TD]February[/TD]
[TD]2014[/TD]
[TD]Team A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Peach[/TD]
[TD]January[/TD]
[TD]2015[/TD]
[TD]Team B[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Orange[/TD]
[TD]January[/TD]
[TD]2015[/TD]
[TD]Team A[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Apple[/TD]
[TD]February[/TD]
[TD]2014[/TD]
[TD]Team A[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Orange[/TD]
[TD]January[/TD]
[TD]2014[/TD]
[TD]Team A[/TD]
[/TR]
</tbody>[/TABLE]


Here's what I am putting in, can you tell me where I'm off?

=SUM(IF(FREQUENCY(IF(Data!B:B=A2,IF(Data!C:C=B1,IF(Data!D:D=D2,Data!A:A))),Data!A:A)>0,1))

Entered as an Array with CTRL-SHFT-ENTER

Thoughts!

Thanks!

 

[Aladin Akyurek]

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

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

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

1​

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

2014​

[/td][td]

2015​

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

[tr][td]

2​

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

2​

[/td][td]

1​

[/td][td]Team A[/td][/tr]

[tr][td]

3​

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

3​

[/td][td]

0​

[/td][td]Team A[/td][/tr]

[tr][td]

4​

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

[tr][td]

5​

[/td][td]Customer[/td][td]Month[/td][td]Year[/td][td]Team[/td][/tr]

[tr][td]

6​

[/td][td]Apple[/td][td]January[/td][td]

2014​

[/td][td]Team A[/td][/tr]

[tr][td]

7​

[/td][td]Peach[/td][td]February[/td][td]

2014​

[/td][td]Team A[/td][/tr]

[tr][td]

8​

[/td][td]Apple[/td][td]January[/td][td]

2014​

[/td][td]Team A[/td][/tr]

[tr][td]

9​

[/td][td]Apple[/td][td]January[/td][td]

2015​

[/td][td]Team B[/td][/tr]

[tr][td]

10​

[/td][td]Orange[/td][td]February[/td][td]

2014​

[/td][td]Team A[/td][/tr]

[tr][td]

11​

[/td][td]Peach[/td][td]January[/td][td]

2015​

[/td][td]Team B[/td][/tr]

[tr][td]

12​

[/td][td]Orange[/td][td]January[/td][td]

2015​

[/td][td]Team A[/td][/tr]

[tr][td]

13​

[/td][td]Apple[/td][td]February[/td][td]

2014​

[/td][td]Team A[/td][/tr]

[tr][td]

14​

[/td][td]Orange[/td][td]January[/td][td]

2014​

[/td][td]Team A[/td][/tr]
[/table]

B2, control+shift+enter, not just enter, copy across to C2, and copy down:

=SUM(IF(FREQUENCY(IF($A$6:$A$14<>"",
     IF($C$6:$C$14=B$1,IF($B$6:$B$14=$A2,
     IF($D$6:$D$14=$D2,MATCH($A$6:$A$14,$A$6:$A$14,0))))),
     ROW($A$6:$A$14)-ROW($A$6)+1),1))

Try to avoid referencing whole columns in the foregoing type of (array-processing) formulas.

 

[a76marine]

I owe you a beer, or a soda, or whatever's your thing. Thank you!

 

[Aladin Akyurek]

I owe you a beer, or a soda, or whatever's your thing. Thank you!

Glad to help.

 

[anmol]

I have a bill wise sale data in which i want to count how many times a category is coming.


Setting is as follows:
Col A: Bill number (27000 + rows including duplicates, and 2000 + rows unique)
Col C: Time period of sale (4 time periods)
Col D: Product code/name (multiple)
Col H: Category (18 categories)


Objective is to find non duplicate unique bill count (Col A), for all 18 categories (Col


H) set in rows, and 4 time periods set in columns


Pls. help with a formula


Thanks

 

[Aladin Akyurek]

K2 houses a category of interest.
L1:O1 houses the periods.

The range in A is named BillNum.
The range in C is named Period.
The range in H is named Category.

Ivec is defined via Formulas | Name Manager as referring to:

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

Now invoke:

L2, control+shift+enter, not just enter, copy across, and down:

=SUM(IF(FREQUENCY(IF(BillNum<>"",IF(Period=L$1,IF(Category=$K2,
    MATCH(BillNum,BillNum,0)))),Ivec),1))

The foregoing can be resource-consuming. In case this is intolerable, we can try a set up with concatenation.

 

[Diztanz]

Ok so I’m having a very similar problem to the many before me. I very much like to try and figure out these sort of things on my own, but I’m stumped and in a bind. I hate to beat a dead horse and add to this discussion but here is my problem.

I have a work sheet named “CutReport” that contains a list of work that is completed.
In another sheet I’m attempting to get some counts based on a few criteria. Within this “Tally” sheet there is a place to enter the date that will be counted. I’m attempting to count the unique ID numbers based on the area and the complete date.

Now I know with a few helper columns I can accomplish this but I’m trying to knock down the number of formulas within the work book and would like to give this route a go.

Small Data Example

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]ID Number
[/TD]
[TD]Item ID
[/TD]
[TD]Area
[/TD]
[TD]Complete Date
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]221555-01123
[/TD]
[TD]A
[/TD]
[TD]A001
[/TD]
[TD]6/1/15
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]221555-01123
[/TD]
[TD]B
[/TD]
[TD]A001
[/TD]
[TD]6/1/15
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]221555-01128
[/TD]
[TD]A
[/TD]
[TD]B001
[/TD]
[TD]6/1/15
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]221555-01130
[/TD]
[TD]A
[/TD]
[TD]B002
[/TD]
[TD]6/1/15
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]221665-01123
[/TD]
[TD]A
[/TD]
[TD]A002
[/TD]
[TD]6/1/15
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]221665-01123
[/TD]
[TD]B
[/TD]
[TD]A002
[/TD]
[TD]6/1/15
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]221665-01123
[/TD]
[TD]C
[/TD]
[TD]A002
[/TD]
[TD]6/1/15
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]221665-08806
[/TD]
[TD]A
[/TD]
[TD]A003
[/TD]
[TD]6/5/15
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]221665-08806
[/TD]
[TD]B
[/TD]
[TD]A003
[/TD]
[TD]6/5/15
[/TD]
[/TR]
</tbody>[/TABLE]

In the tally sheet Date box I’ve entered 6/1/15
So my results should be…
Area A Unique ID numbers = 2
Area B Unique ID Numbers= 1

I should also mention that there may be blank rows that I’d like to not add into the count but I believe I can figure a way around that if need be.

Any help would be greatness,
Thank you for your time-

 

[Aladin Akyurek]

@Diztanz

You write: "Area A Unique ID numbers = 2". Did you mean Item ID?

 

[Diztanz]

No sir, I'm trying to count the unique ID Numbers (Col. A) based on if The complete date(Col. D) matches the selected date in the Tally Sheet But seperated by the Alpha portion of the Area(Col. C)

The Area is actually the Alpha part of the Area data, but there are many different occurances of them which is where the Number part comes into play. I probably could have left the Item ID column out of the example, but was trying to show that there are multiples of the ID Number (only difference is the Item ID)

Im terribily sorry if i was unclear.

 

[Aladin Akyurek]

No sir, I'm trying to count the unique ID Numbers (Col. A) based on if The complete date(Col. D) matches the selected date in the Tally Sheet But seperated by the Alpha portion of the Area(Col. C)

The Area is actually the Alpha part of the Area data, but there are many different occurances of them which is where the Number part comes into play. I probably could have left the Item ID column out of the example, but was trying to show that there are multiples of the ID Number (only difference is the Item ID)

Im terribily sorry if i was unclear.

[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][td]

G​

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

1​

[/td][td]ID Number[/td][td]Item ID[/td][td]Area[/td][td]Complete Date[/td][td][/td][td]Item ID[/td][td]

6/1/2015​

[/td][/tr]

[tr][td]

2​

[/td][td]221555-01123[/td][td]A[/td][td]A001[/td][td]

6/1/2015​

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

2​

[/td][/tr]

[tr][td]

3​

[/td][td]221555-01123[/td][td]B[/td][td]A001[/td][td]

6/1/2015​

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

2​

[/td][/tr]

[tr][td]

4​

[/td][td]221555-01128[/td][td]A[/td][td]B001[/td][td]

6/1/2015​

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

[tr][td]

5​

[/td][td]221555-01130[/td][td]A[/td][td]B002[/td][td]

6/1/2015​

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

[tr][td]

6​

[/td][td]221665-01123[/td][td]A[/td][td]A002[/td][td]

6/1/2015​

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

[tr][td]

7​

[/td][td]221665-01123[/td][td]B[/td][td]A002[/td][td]

6/1/2015​

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

[tr][td]

8​

[/td][td]221665-01123[/td][td]C[/td][td]A002[/td][td]

6/1/2015​

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

[tr][td]

9​

[/td][td]221665-08806[/td][td]A[/td][td]A003[/td][td]

6/5/2015​

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

[tr][td]

10​

[/td][td]221665-08806[/td][td]B[/td][td]A003[/td][td]

6/5/2015​

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

G2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:$A$10<>"",
    IF(ISNUMBER(SEARCH("|"&$F2,"|"&$C$2:$C$10)),IF($D$2:$D$10=$G$1,
    MATCH($A$2:$A$10,$A$2:$A$10,0)))),ROW($A$2:$A$10)-ROW($A$2)+1),1))