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.

 

[SyedGangsta]

You are welcome. Thanks for the kind feedback. See the following link helps:http://www.mrexcel.com/forum/excel-...ting-summing-based-2-columns.html#post3000815.

Hi Aladin,

Sorry for re-opening this query, after testing on the full data set today, I have found a issue, which is that the formula seems to count unique visitor in a whole month rather than for each day for the selected month.

I have shown this in the example,

visitor ABC3412 visits twice on 1 Dec is correctly counted once
Visitor ABC3412 visits again on 2 Dec, should be counted but is not.

I wonder if this can be resolved?
[TABLE="width: 470"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]visitor[/TD]
[TD]date[/TD]
[TD]month[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D901822[/TD]
[TD]01-Nov-14[/TD]
[TD]Nov-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC3412[/TD]
[TD]01-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC3412[/TD]
[TD]01-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9123455[/TD]
[TD]01-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC3412[/TD]
[TD]02-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]01-Nov-14[/TD]
[TD="colspan: 2"]Unique Visitors for Nov 14=[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]01-Dec-14[/TD]
[TD="colspan: 2"]Unique Visitors for Dec 14=[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Visits for Dec 14 should be 3.

The formula in cell D8 is :

{=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6-DAY($B$2:$B$6)+1=$A8,MATCH($A$2:$A$6,$A$2:$A$6,0))),ROW($A$2:$A$6)-ROW($A$2)+1),1))}

Thanks again for any assistance.

 

[Aladin Akyurek]

Hi Aladin,

Sorry for re-opening this query, after testing on the full data set today, I have found a issue, which is that the formula seems to count unique visitor in a whole month rather than for each day for the selected month.

I have shown this in the example,

visitor ABC3412 visits twice on 1 Dec is correctly counted once
Visitor ABC3412 visits again on 2 Dec, should be counted but is not.

I wonder if this can be resolved?
[TABLE="width: 470"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]visitor[/TD]
[TD]date[/TD]
[TD]month[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]D901822[/TD]
[TD]01-Nov-14[/TD]
[TD]Nov-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ABC3412[/TD]
[TD]01-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ABC3412[/TD]
[TD]01-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9123455[/TD]
[TD]01-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ABC3412[/TD]
[TD]02-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]01-Nov-14[/TD]
[TD="colspan: 2"]Unique Visitors for Nov 14=[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]01-Dec-14[/TD]
[TD="colspan: 2"]Unique Visitors for Dec 14=[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]

Visits for Dec 14 should be 3.

The formula in cell D8 is :

{=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6-DAY($B$2:$B$6)+1=$A8,MATCH($A$2:$A$6,$A$2:$A$6,0))),ROW($A$2:$A$6)-ROW($A$2)+1),1))}

Thanks again for any assistance.

It has been set up per month/year as described.

It appears you want the count per day...

=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",
    IF($B$2:$B$6=$A8,MATCH($A$2:$A$6,$A$2:$A$6,0))),
    ROW($A$2:$A$6)-ROW($A$2)+1),1))

where A8 1-Nov-14, A9 2-Nov-14 or 1-Dec-14, etc.
Are you aware of the fact each year counts 364 days (dates), requiring that many formulas?

 

[SyedGangsta]

It has been set up per month/year as described.

It appears you want the count per day...

=SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",
    IF($B$2:$B$6=$A8,MATCH($A$2:$A$6,$A$2:$A$6,0))),
    ROW($A$2:$A$6)-ROW($A$2)+1),1))

where A8 1-Nov-14, A9 2-Nov-14 or 1-Dec-14, etc.
Are you aware of the fact each year counts 364 days (dates), requiring that many formulas?

Yes, my last resort was that I would layout each date and use SumProduct along with frequency to find total number of unique visitors per date, I can then run a further query to find out the total number of visitors for the month.

I just became interested to see if it was possible to count visitors each month but ignoring those that made more than one visit on the same day using the frequency function in one formula, may be its not possible, but it seem like it should be possible using it in combination with another array function such as SumProduct or IFS.

For the record, I was avoiding SumProduct due to the fact it does slow down to a crawl when applied to ranges of 20k+ rows.


Thanks for trying to solve this, I will update this thread if I do find a way that works well.

 

[Aladin Akyurek]

Yes, my last resort was that I would layout each date and use SumProduct along with frequency to find total number of unique visitors per date, I can then run a further query to find out the total number of visitors for the month.

I just became interested to see if it was possible to count visitors each month but ignoring those that made more than one visit on the same day using the frequency function in one formula, may be its not possible, but it seem like it should be possible using it in combination with another array function such as SumProduct or IFS.

For the record, I was avoiding SumProduct due to the fact it does slow down to a crawl when applied to ranges of 20k+ rows.


Thanks for trying to solve this, I will update this thread if I do find a way that works well.

Don't follow. What are you trying to say?

 

[VBABEGINER]

What are you summing under which conditions?

HELLO ALADIN AKYUREK SIR,

REASON TO REPLY ON THIS THERAD, BEACAUSE I HAVE ALSO SAME QUERY.

I HAVE DATA LIKE

ABC, PQR
PQR,ST
ADC, FG, QWE
AD,PL,SQL

I WANT TO FIND OUT THE COUNT OF TEXT INSIDE IN EACH CELL WHICH ARE SEPERATED BY "," OR "space". I dont understand what way should started thinking on this..I need your expert help sir..

 

[Aladin Akyurek]

@VBABEGINER

What is the result for

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

A​

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

2​

[/td][td]ABC, PQR[/td][/tr]

[tr][td]

3​

[/td][td]PQR,ST[/td][/tr]

[tr][td]

4​

[/td][td]ADC, FG, QWE[/td][/tr]

[tr][td]

5​

[/td][td]AD,PL,SQL[/td][/tr]
[/table]

the sample you posted?

 

[VBABEGINER]

count of text inside
2
2
3
3

 

[Aladin Akyurek]

count of text inside
2
2
3
3

Looks like...

=IF(A2="",0,LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)

is what you are after.

 

[Brothwood]

Try

=SUM(IF(FREQUENCY(IF(C2:C6="M",IF(A2:A6=1,IF(B2:B6<>"",MATCH(B2:B6,B2:B6,0)))),ROW(B2:B6)-ROW(B2)+1),1))

confirmed with CTRL+SHIFT+ENTER

Hi

I Have been trying to use the above formula to count the unique values for products i have tested during a week.
Everything i test a product it gets added to a database and sometimes i test the same unit twice in one week so i need to get unique number for that week.
I cant just do a remove duplicate function as it will also remove data from following weeks which i will need to include.

The data base looks like this
[TABLE="width: 500"]
<tbody>[TR]
[TD]week number[/TD]
[TD]product[/TD]
[TD]serial number[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]alpha[/TD]
[TD]a1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]alpha[/TD]
[TD]a2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]alpha[/TD]
[TD]a1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]beta[/TD]
[TD]b1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]beta[/TD]
[TD]b2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]alpha[/TD]
[TD]a3[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]charlie[/TD]
[TD]c1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]charlie[/TD]
[TD]c2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]beta[/TD]
[TD]b1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]beta[/TD]
[TD]b2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]alpha[/TD]
[TD]a4[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]alpha[/TD]
[TD]a2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]beta[/TD]
[TD]b2[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]charlie[/TD]
[TD]c1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]beta[/TD]
[TD]b3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]alpha[/TD]
[TD]a4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]alpha[/TD]
[TD]a5[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]beta[/TD]
[TD]b3[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]beta[/TD]
[TD]b4[/TD]
[/TR]
</tbody>[/TABLE]

The code i was using is

=SUM(IF(FREQUENCY(IF(A:A=6,IF(B:B="ALPHA",IF(C:C<>"",MATCH(C:C,C:C,0)))),ROW(C:C,-MIN(ROW(C:C))+1),1))

The correct answer should be 3
a1, a2, a4. but im getting either 0 or an error with the "+1" part.

Could someone please help as i have tried this as well as other formulae but cant find anything to get the desired result.

Regards

 

[barry houdini]

Welcome to MrExcel Brothwood,

You have a comma in place of a parenthesis.....and you really should restrict the range because these type of formulas can be slow when referencing whole columns, try this version

=SUM(IF(FREQUENCY(IF(A2:A1000=6,IF(B2:B1000="ALPHA",IF(C2:C1000<>"",MATCH(C2:C1000,C2:C1000,0)))),ROW(C2:C1000)-MIN(ROW(C2:C1000))+1),1))

confirmed with CTRL+SHIFT+ENTER

adjust ranges if required