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.

 

[Aladin Akyurek]

Thats disappointing. Is there something different about what I'm trying to do compared to the other examples here? I really wouldn't have a clue where to start with VBA/SQL for this problem.

For a separate issue, I've managed to use SUM(IF in an array and SUMPRODUCT to count multiple conditions using the same 50,000 rows. My main difficultly here is trying to only count these conditions alongside the unique values specified in column A.

Even if it it would be slow, I'd appreciate help in giving it a shot! I'm out of other options.

Given the sample you posted...

[TABLE="width: 457"]
<COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4039" width=114><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2958" width=83><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 3953" width=111><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3783" width=106><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4010" width=113><TBODY>[TR]
[TD="class: xl65, width: 82, bgcolor: transparent"]Field-1[/TD]
[TD="class: xl65, width: 114, bgcolor: transparent"]Field-2[/TD]
[TD="class: xl65, width: 83, bgcolor: transparent"]Field-3[/TD]
[TD="class: xl65, width: 111, bgcolor: transparent"]Field-4[/TD]
[TD="class: xl65, width: 106, bgcolor: transparent"]Field-5[/TD]
[TD="class: xl65, width: 113, bgcolor: transparent"]Field-6[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]90010[/TD]
[TD="class: xl67, bgcolor: transparent"]Admission[/TD]
[TD="class: xl67, bgcolor: transparent"]HospA[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]29-Nov-12[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]1-Apr-13[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]5-May-13[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]90010[/TD]
[TD="class: xl67, bgcolor: transparent"]Discharge[/TD]
[TD="class: xl67, bgcolor: transparent"]HospA[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]30-Nov-12[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]7-Jan-12[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]2-Jan-13[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent, align: right"]90011[/TD]
[TD="class: xl67, bgcolor: transparent"]Admission[/TD]
[TD="class: xl67, bgcolor: transparent"]HospA[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]12-Apr-14[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]8-Dec-13[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]13-Jul-13[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]90013[/TD]
[TD="class: xl67, bgcolor: transparent"]Discharge[/TD]
[TD="class: xl67, bgcolor: transparent"]HospA[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]4-Mar-13[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]27-Jul-13[/TD]
[TD="class: xl69, bgcolor: transparent, align: right"]29-Dec-13[/TD]
[/TR]
</TBODY>[/TABLE]

What are the conditions and on which field do we apply/run the distinct count? Assume that the above sample is located in A1:F5, the headers included.

 

[Satele]

The data in A1:F5 is in sheet Data1. In cell D6 of the 'Report' sheet, I'd like to return the number of records (rows) where Data1B1:B5 = 'Admission', Data1!C2:C5 = the value in Report!D5, Data1!D2:D5 <> "", and finally where these conditions are met, the number of records where the value in Data1!A2:A5 is unique (only occurs once).

So, using this table as an example, if Report!D5 = 'HospA', the formula would return a value of 2 (counting rows 2 and 4). This is because, even though record '90010' occurs twice, it only matches the condition of B2:B5 = 'Admission' once.

Two additional problems I'm having, if you could shed any light on...

In Report!E5, I need to find the number of records (rows) where Data1B1:B5 = 'Discharge', Data1!C2:C5 = the value in Report!D5, Data1!D2:D5 <> "", and the difference between two dates in columns E and F is between 0-2 weeks.
In Report!F5, I need to return records that match the conditions as set out in the first problem above, but rather than return the number of records (2 in the example I gave), I need to return the actual value(s) in Column A. More than 1 row would meet this criteria so I imagine that I would then copy this formula into cells below to return the all the rows that match the conditions? For the example I gave above, the values returned would be '90010' and '90011'.
Anything you can provide here would be very much appreciated!

 

[Aladin Akyurek]

Can't we just talk in terms of the sample? And one problem at a time.

We now have:

Field-2 (B2:B5) must equal "Admission".
Field-3 (C2:C5) must equal "HospA".
Field-4 (D2:D5) must contain a date (must not be empty).

Distinct count must run Filed-1 (A2:A5) under the preceding 3 conditions.

Let H2 house Admission and I2 HospA.

If you enter the following formula

=COUNTIFS(B2:B5,H2,C2:C5,I2,D2:D5,"<>")

in J2, we get 2 as result. This is just a multiconditional record count!
What makes you think that we should run a distinct count on A2:A5?

 

[Satele]

Can't we just talk in terms of the sample? And one problem at a time.

We now have:

Field-2 (B2:B5) must equal "Admission".
Field-3 (C2:C5) must equal "HospA".
Field-4 (D2:D5) must contain a date (must not be empty).

Distinct count must run Filed-1 (A2:A5) under the preceding 3 conditions.

Let H2 house Admission and I2 HospA.

If you enter the following formula

=COUNTIFS(B2:B5,H2,C2:C5,I2,D2:D5,"<>")

in J2, we get 2 as result. This is just a multiconditional record count!
What makes you think that we should run a distinct count on A2:A5?

Ok, no worries.

The reason why I need a distinct count on A2:A5 is because the data I provided is just a short anonymised sample. For example, record number 90010 may exist 10 times in column A, but I only want to count it if that record is not associated to a 'discharge', 'transfer', or anything not equal to 'admission' in column B.

For this count, I'm only interested in the records which only have an 'admission' entry in column B as that's the only one way to count number of people admitted but have not yet been moved on from admissions. If a record number, say 90010, occurs more than once it will be because that person has moved on from admissions.

Perhaps I didn't explain it well! Do you understand what I'm trying to do now?

Thanks again.

p.s looking at it again. I've confused the issue with the sample data by saying that the 90010 record should be counted. It should not, as it has a discharge record as well as an admission record and I'm just trying to count those that don't have anything other than an admission record.

 

[Aladin Akyurek]

Ok, no worries.

The reason why I need a distinct count on A2:A5 is because the data I provided is just a short anonymised sample. For example, record number 90010 may exist 10 times in column A, but I only want to count it if that record is not associated to a 'discharge', 'transfer', or anything not equal to 'admission' in column B.

For this count, I'm only interested in the records which only have an 'admission' entry in column B as that's the only one way to count number of people admitted but have not yet been moved on from admissions. If a record number, say 90011, occurs more than once it will be because that person has moved on from admissions.

Perhaps I didn't explain it well! Do you understand what I'm trying to do now?

Thanks again.

No. When you provide a sample, try to give one that is representative for your data. That said:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$5<>"",IF($B$2:$B$5=$H2,
  IF($C$2:$C$5=$I2,IF(ISNUMBER($D$2:$D$5),
  MATCH($A$2:$A$5,$A$2:$A$5,0))))),ROW($A$2:$A$5)-ROW($A$2)+1),1))

This will eat lots of resources.

 

[Satele]

No. When you provide a sample, try to give one that is representative for your data. That said:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($A$2:$A$5<>"",IF($B$2:$B$5=$H2,
  IF($C$2:$C$5=$I2,IF(ISNUMBER($D$2:$D$5),
  MATCH($A$2:$A$5,$A$2:$A$5,0))))),ROW($A$2:$A$5)-ROW($A$2)+1),1))

This will eat lots of resources.

Apologies for that; it's been a long day. I'll try this and let you know how I get on. Much appreciated!

 

[Domsche]

Hi there Aladin,

i read the whole thread but still cannot get my Formula to work:

I have the following Spreadsheet


[TABLE="width: 500"]
<tbody>[TR]
[TD]Card[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]VKC02000204 [/TD]
[TD]CB[/TD]
[/TR]
[TR]
[TD]VKC02000204 [/TD]
[TD]QF[/TD]
[/TR]
[TR]
[TD]VKC02000204 [/TD]
[TD]CB[/TD]
[/TR]
[TR]
[TD]KABB020108 [/TD]
[TD]CB[/TD]
[/TR]
[TR]
[TD]EHKD03000203 [/TD]
[TD]CB[/TD]
[/TR]
[TR]
[TD]KABB020108 [/TD]
[TD]ST[/TD]
[/TR]
</tbody>[/TABLE]


Now i want to only count the Card-IDs that have the Comment "CB" in them and count them as unique (just like the other users in this thread). I tought i got a grip on the formula after reading all this but i still cannot get it to work.

here is my formula (entered as a ArrayFormula):

SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6="*CB*",
MATCH("~"&$A$2:$A$6,$A$2:$A$6&"",0))),ROW($A$2:$C$6)-ROW($A$2)+1),1)))

The Result for the Formula is 0 atm but it should be 2

I hope you can help me

 

[Aladin Akyurek]

Hi there Aladin,

i read the whole thread but still cannot get my Formula to work:

I have the following Spreadsheet


[TABLE="width: 500"]
<TBODY>[TR]
[TD]Card
[/TD]
[TD]Comment
[/TD]
[/TR]
[TR]
[TD]VKC02000204
[/TD]
[TD]CB
[/TD]
[/TR]
[TR]
[TD]VKC02000204
[/TD]
[TD]QF
[/TD]
[/TR]
[TR]
[TD]VKC02000204
[/TD]
[TD]CB
[/TD]
[/TR]
[TR]
[TD]KABB020108
[/TD]
[TD]CB
[/TD]
[/TR]
[TR]
[TD]EHKD03000203
[/TD]
[TD]CB
[/TD]
[/TR]
[TR]
[TD]KABB020108
[/TD]
[TD]ST
[/TD]
[/TR]
</TBODY>[/TABLE]


Now i want to only count the Card-IDs that have the Comment "CB" in them and count them as unique (just like the other users in this thread). I tought i got a grip on the formula after reading all this but i still cannot get it to work.

here is my formula (entered as a ArrayFormula):

SUM(IF(FREQUENCY(IF($A$2:$A$6<>"",IF($B$2:$B$6="*CB*",
MATCH("~"&$A$2:$A$6,$A$2:$A$6&"",0))),ROW($A$2:$C$6)-ROW($A$2)+1),1)))

The Result for the Formula is 0 atm but it should be 2

I hope you can help me

I don't think you need a 'contains' test (The * wildcard around CB means: Contains CB), rather an equal test. That taken into account, you would have it set up as needed in the sitiuauation your sample implicates:

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

 

[SyedGangsta]

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

Good Afternoon,

I hope you do forgive me for resurrecting this old thread, but I felt your formula as quoted is almost perfect for a problem that I have.

The only thing that is preventing me from using your formula is that in my case, column A has a string value that can be a mix of text and numbers i.e. D900000 or ABC5111 as well as plain numbers such as 90001.

My ranges will be quite big up-to 20000 rows, so I expect I will get a slow down due to the size of the array, but I cant see a better approach, I did try SumProduct, and it was very slow indeed.

Please see example below to help understand what I am trying to do.

[TABLE="width: 343"]
<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]123456D[/TD]
[TD]02-Dec-14[/TD]
[TD]Dec-14[/TD]
[TD]■[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]■[/TD]
[TD]■[/TD]
[TD]■[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="colspan: 3"]Unique Visitors for Nov 14=[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="colspan: 3"]Unique Visitors for Dec 14=[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


I would be very grateful for any advice I get on this.

 

[SyedGangsta]

In responce to my query, I think I may have a solution...

{=SUM(IF(FREQUENCY(IF(Month=$H$3,IF(Visitors<>"",IF(C3:C7<>"",MATCH(date,date,0)))),ROW(date)-ROW(C3)+1),1))}