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]

Sorry after testing it out, the above formula does not work as it now will allow 1 visitor per date.. please ignore, my solution, I still need help!

 

[Aladin Akyurek]

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.

A8: 1-Nov-14
A9: 1-Dec-14

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

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

 

[SyedGangsta]

Thank you for such a quick response, I will test this formula and see what happens, although there might be a mis-understanding in how I explained.

What I am trying to do is try to get total visitors for the whole month but not count visitors that make a repeat visit on the same day hence not being able to use SumProduct method.

Cell A8 would be Nov-14, A9 Dec-14 so that I can test the condition with Column C.



Many thanks for your help.

 

[Aladin Akyurek]

Thank you for such a quick response, I will test this formula and see what happens, although there might be a mis-understanding in how I explained.

What I am trying to do is try to get total visitors for the whole month but not count visitors that make a repeat visit on the same day hence not being able to use SumProduct method.

Cell A8 would be Nov-14, A9 Dec-14 so that I can test the condition with Column C.



Many thanks for your help.

What formula does is to convert all November dates of 2014 to 1 November 2014. That is, it tests for Nov-14.

 

[SyedGangsta]

What formula does is to convert all November dates of 2014 to 1 November 2014. That is, it tests for Nov-14.

WOW....your a life saver, thank you so much, your solution works a treat..not tried it on the full dataset but I am confident it will work!

I must admit the formula is too much for my underdeveloped mind, shows the level of your professionalism this area, kudos to you!

 

[Aladin Akyurek]

WOW....your a life saver, thank you so much, your solution works a treat..not tried it on the full dataset but I am confident it will work!

I must admit the formula is too much for my underdeveloped mind, shows the level of your professionalism this area, kudos to you!

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

 

[danfolt]

Hello I would like to ask if someone can help me, I want to create a excel bar-hotel inventory with the help of barcode reader, when we sell a drink it gives me a barcode number and next to it timestamp, on right side columns I get unique products, all I need is to extract every product sold every single day for all year possibly unique products too, you can see everything in the image below. Can somebody help me? I can also publish excel file if interested

 

[Aladin Akyurek]

@danfolt

Images do not provide data which can be read into Excel. Instead of the whole book, try to post in a readable form a sample that is descriptive/representative of your data/question.

 

[danfolt]

thanks @Aladin i will try my best to explain my problem

the form is here
1 ----A ----------- B-------- C
2 barcode01 28-01-15 Caffe espresso
3 barcode01 28-01-15 Caffe espresso
4 barcode01 28-01-15 Caffe espresso
5 barcode02 28-01-15 Caffe espresso
6 barcode02 28-01-15 Caffe espresso
7 barcode01 29-01-15 Caffe espresso
8 barcode01 29-01-15 Caffe espresso
9 barcode03 29-01-15 Caffe espresso
10 barcode03 30-01-15 Caffe espresso
11 barcode03 30-01-15 Caffe espresso


I would like to get this:


1 ----D--------------- E -----------F ----------G ------- ---- H -------- I ----------
2 28-01-15 number of products 29-01-15 number of products 30-01-15 number of products
3 barcode01 -----3 ----------barcode01 ----- 2 --------- barcode03 -------- 2
4 barcode02 ----- 2 ----------barcode03 ----- 1


I would like to get names and number of products sold every day (28-01-15, 29-01-15, 30-01-15)
What I don't know is how to get unique products per every day when the barcode is read with criteria (28-01-15 ecc.)

so can you pls. somebody help me to know what function to put in D3 E3 F3 G3 ecc

 

[Aladin Akyurek]

thanks @Aladin i will try my best to explain my problem
[...]

Well done. Thanks.

Assign a name, say BarData, to A2:A11, using Formulas | Name Manager.
Assign Date as name to B2:B11.
Assign Product as name to C2:C11.
Define Ivec using Formulas | Name Manager as referring to:

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

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

=IFERROR(INDEX(BarData,SMALL(IF(FREQUENCY(IF(BarData<>"",
   IF(Date=D$2,MATCH(BarData,BarData,0))),Ivec),Ivec),
   ROWS(D$3:D3))),"")

E3, copied down:

=

=IF($D3="","",COUNTIFS(BarData,D$3,Date,D$2))

Repeat these two formulas, appropriately adjusted, in F3:G3 and H3:I3.